Show Previous Quarter (sales) as a Calculated Field based on the current quarter that is selected

[rupaliqlik]

Hi Folks.

What I would like to do is add another column called [Previous QTR Sales] and then when:

     The user selects:

• Q1 the [Sales] column will show sales for Q1 and [Previous QTR Sales] shows Q4 previous year Sales. *there is no data for this so i cannot check but just want to mention it because this will be a problem for me in 2020 Q1.
• Q2 the [Sales] column will show sales for Q2 and [Previous QTR Sales] Shows Q1 sales
• Q3 the [Sales] column will show sales for Q2 and [Previous QTR Sales] Shows Q2 sales
• Q4 the [Sales] column will show sales for Q2 and [Previous QTR Sales] Shows Q3 sales

I thought this would be easy but nothing I have read or found so far works and keeps me from having to up date a parameter every Quarter / Year.

Thank you in advance for your help!

Rupali E

 kaushik.solanki 

sunny t

[olivetwist]

Are you creating a master calendar table? Are you setting any variables like the following?

Let vCurr_Yr = Year(Today());  Let vCurr_Mo = Month(Today()); Let vCurr_Qtr = Ceil(Month(Today())/3);

Establishing a master calendar would be best in my opinion, but if you are using variables you can say:

Let vPrev_Qtr = If(vCurr_Qtr = 1, 4, vCurr_Qtr-1)

and

Let vPrev_Yr = If(vCurr_Qtr = 1, 4, vCurr_Yr-1)

Then use those variables in your expressions with Set Analysis:

Sum({<Year={$(vPrev_Yr)},Quarter={$(vPrev_Qtr)}>} Sales)

[Brett_Bleess]

Design Blog post regarding how to create a Master Calendar that may be helpful:

https://community.qlik.com/t5/Qlik-Design-Blog/The-Master-Calendar/ba-p/1471527

Regards,
Brett