ad

Scenario 1 (Mapping 5 parameters to top 5 Quarters)

Scenario 1 (Medium)

Problem: we need to map each parameters to corresponding quarter to calculate estimated uplift sales.

Description:
Lets take sample superstore subset data set. I am more interested to see top 5 quarters of sales in my data set. I plot a line graph like below:

13Q4, 13Q3, 13Q2, 13Q1 and 12Q4 are my latest quarters in present dataset.

Note: assume that 13Q4 as my current quarter and follows other as
13Q3 as LQ1
13Q2 as LQ2
13Q1 as LQ3
12Q4 as LQ4
Now I want to show one more line with estimated % of increase in sales. I will pass estimated % values for each Quarter using parameters.

My Parameters list:
Estimated % for CQ
Estimated % for LQ1
Estimated % for LQ2
Estimated % for LQ3
Estimated % for LQ4
Now I need to calculate Estimated sales for each quarter using following formula

if attr(quarter) = '13Q4' then sum(sales) + (sum(sales) * (Estimated % for CQ/100))
elseif attr(quarter) = '13Q3' then sum(sales) + (sum(sales) * (Estimated % for LQ1/100))
elseif attr(quarter) = '13Q2' then sum(sales) + (sum(sales) * (Estimated % for LQ2/100))
elseif attr(quarter) = '13Q1' then sum(sales) + (sum(sales) * (Estimated % for LQ3/100))
elseif attr(quarter) = '12Q1' then sum(sales) + (sum(sales) * (Estimated % for LQ4/100))
END

The above formula should run for each row in the data set. But we cant hard code these quarters like 13Q4, 13Q3 …...12Q1 because these data will come dynamically from data source. In future you may see 14Q4, 14Q3....13Q1

Here is the problem how to know which quarter is coming in current row? I mean is CQ or LQ1 or LQ2 or LQ3 or LQ4?

Solution:
After Knowing the solution you might feel simple. But this logic I got after several trials.

First create a 5 parameters with following names
Estimated % for CQ
Estimated % for LQ1
Estimated % for LQ2
Estimated % for LQ3
Estimated % for LQ4
Just remember once again the calculate field for Estimated uplift sales:

if attr(quarter) = '13Q4' then sum(sales) + (sum(sales) * (Estimated % for CQ/100))
elseif attr(quarter) = '13Q3' then sum(sales) + (sum(sales) * (Estimated % for LQ1/100))
elseif attr(quarter) = '13Q2' then sum(sales) + (sum(sales) * (Estimated % for LQ2/100))
elseif attr(quarter) = '13Q1' then sum(sales) + (sum(sales) * (Estimated % for LQ3/100))
elseif attr(quarter) = '12Q1' then sum(sales) + (sum(sales) * (Estimated % for LQ4/100))
END

In Above formula we need to replace 13Q4,13Q3 …..12Q1 dynamically. Let think that if I give positions for top 5 quarters as below

Top Quarter in data set ---> position as 1 and follows see below example

13Q4 ----> 1 and see I can map this to CQ parameter
13Q3 ----> 2 this to LQ1 parameter
13Q2 ----> 3 this to LQ2 parameter
13Q1 ----> 4 this to LQ3 parameter
12Q4 ----> 5 this to LQ4 parameter

how to give positions to Quarters?

Using rank function we can give positions to quarters dynamically.

Create a calculate field as “Rank Quarter” using below formula.

rank(attr([Quarter]))

now you will see like this

this rank will update for future quarters.


Now we will write calculation field for Estimated uplift sales using Rank Quarter field.

if [Rank Quarter] = 1 then sum(Sales) + (sum(Sales) * ([Estimated % for CQ]/100))
elseif [Rank Quarter] = 2 then sum(Sales) + (sum(Sales) * ([Estimated % for LQ1]/100))
elseif [Rank Quarter] = 3 then sum(Sales) + (sum(Sales) * ([Estimated % for LQ2]/100))
elseif [Rank Quarter] = 4 then sum(Sales) + (sum(Sales) * ([Estimated % for LQ3]/100))
elseif [Rank Quarter] = 5 then sum(Sales) + (sum(Sales) * ([Estimated % for LQ4]/100))
END

Now place the columns in respective shelves and make a dual axis chart like below



Note:
I did not include two things above.
1. calculation field to extract “Quarter” column from “Order Date” column

right(str(year([Order Date])),2) + 'Q' + str(datepart('quarter',[Order Date]))

2. Applying top 5 filter on Quarter column to see top 5 quarters















See the report below: