**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: