Calculate Delta,% Increase with Up/Down Arrows on YoY,QoQ,MoM,DoD using Lookup function in Tableau
Why Lookup? What is use of this function?
LOOKUP function is used to look up and down a partition of rows and return a values in that field by specifying an offset. This can be used Lookup previous or next value within resp. partition to compute required calculation.
LOOKUP is table calculation which gets executed in the end as per the order of execution of filters and calculations in Tableau. When delta on YoY/QoQ/MoM is to be computed first and later display only the main number wrt current Year, Lookup as Table calculation filter is also required because it gets executed in end after table calculation i.e Delta on YoY gets computed first and then Lookup filter is applied to show only current Year Sales along with Delta on YoY. This scenario is covered in next article of LOOKUP.
Let’s see how to use LOOKUP to calculate Delta, %Increase and Up/Arrow on QoQ.
- Drag “Order Date” dimension to Rows. Right Click and then Select “Quarter Q2 2015”. Drag “COUNT(Orders)” to TEXT to see Order count for each of quarter as seen below.
2. Now, Let’s calculate Delta, % Increase and % Increase Arrow from “current quarter” wrt “previous 1 quarter”.
3. Create a new calculated field — Delta and write below Lookup logic:
0 is offset that looks up Count of Orders value for Current Row and -1 is offset that looks up 1 Previous row having count of orders value on QoQ.
4. Drag Delta field on rows to do validation of computed Delta using above logic.
For 2016 Q4 and Q3, Delta is = 5926–4780 = 1146 Orders which means that 1146 orders were increased in 2016 Q4 as compared to 2016 Q3.
For 2016 Q1 and 2015 Q4, Delta is = 2742–4298 = -1556 which means that 1556 orders were reduced in 2016 Q1 as compared to 2015 Q4.
5. Create calculations for % Increase and % Increase Arrow Up/Down as below.
6. Drag both these fields to rows to see below output.
7. Now drag and drop %Increase Arrow Up/Down field to Color and assign red color for down arrow and green for Up arrow. Drag % Increase Arrow Up/Down field from Rows to Text and see final results as below.
8. To Calculate YoY,MoM,DoD — We just have to replace Quarter(Order Date) dimension with YEAR(Order Date) or Month(Order Date) to compute YoY and MoM respectively.
