10/9/16

Tableau - Real Time Scenario 1 (Mapping 5 parameters to top 5 Quarters)

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

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

See the report below: