Using LOD expressions & LOOKUP function to compute YoY/QoQ/WoW/MoM/DoD Profits
Requirement: I’ve created below bar chart showing States Vs SUM(Profits) for India. Requirement is to display Up/Down arrow based upon SUM(Profit) by State, YEAR(Order Date) on YoY.
For example, If I select the Year=2016, then difference between SUM(Profits) should be calculated by Year for that particular state only.
Sum(Profits) in 2016 for Maharashtra = Rs.50000
Sum(Profits) in 2015 for Maharashtra = Rs.30000
Then Up arrow should get displayed in front of Maharashtra bar as Rs.50000–Rs.30000= Rs.20000 Profit
Why can’t we use LOOKUP directly:
For this report, we have State (Dim) and Profit (measures) in view. If we simply write LOOKUP function as below, then it will compute Delta of Profits from State value in current row ( for e.g Maharashtra) with State value in previous row(For e.g. Madhya Pradesh) which is not the requirement.
LOOKUP(SUM([Profit]),0)-LOOKUP(SUM([Profit]),-1)
To compute Delta Profit on YOY on resp. State only, follow below Steps:
- Create a Parameter for “Order Date” and add all required YEAR values in it.
2. Create “YoY” calculation wrt to this parameter so that it will return Last 2 Years value based upon selected value in Parameter.
TEST this calculation: When Parameter has 2016 as selected YEAR — then getting “True” for 2015 and 2016.
When Parameter has 2015 as selected YEAR — then getting “True” for 2015 and 2014.
3. Drag “Last 2 Years” Calculation to Filters and set to “True”. With this filter, report is having now data of 2 years always.
4. Now, we have to calculate Profits for each (State, Year). In report, SUM(Profits) is shown on State granularity with filter of “Last 2 Years” showing selected year profits only.
As we don’t have YEAR(Order Date) dimension in view, we have to make use of LOD expressions to compute Profits by Order Date(YEAR),State granularity.
Note: We can make use of “Include” LOD also here instead of FIXED. Any would work.
5. Write LOOKUP using above calculation now.
0th offset refers to Current Selected Year in Parameter and -1 refers to Previous Year Value wrt. Selected year in Parameter.
6. Let’s test above calculation to see if the return desired results.
When Parameter = 2016 Year
When Parameter = 2015 Year
7. Now simply write below calculations to show arrows up/down.
Test these Calculations:
8. Drag “Arrow Up” and “Arrow Down” calculations on Label and assign them different colors. Drag Year(Order Date) to detail and Compute Arrow Up and Down calculations using Year(Order Date).
9. You will get the view like below now:
If you notice, these SUM of Profits getting displayed for each state is actually Sum of Profits of last 2 years, because we have “Last 2 Years” filter on filter shelf.
As per requirement, we want to show SUM(Profit) per State for current selected year values only and display Arrow Up/Down based on YoY.
To do this, Write an LOOKUP filter logic to return only current selected year in Parameter.
Drag this calculation to Filter shelf and Compute using “Table Down”
Why this LOOKUP filter: Lookup filter is table calculation filter which gets executed in the last as per order of execution of Tableau. Before it gets executed, we are calculating YoY logic for arrows having 2 years of data and in the end displaying current Year Profits in bar chart.
For QoQ,Mom,WoW — Change the logic of Last 2 Years and remaining calculations accordingly.
