topic Re: Show Previous Quarter (sales) as a Calculated Field based on the current quarter that is selecte in QlikView

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

rupaliqlik — Sat, 16 Nov 2024 20:31:15 GMT

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

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

olivetwist — Tue, 09 Jul 2019 19:07:07 GMT

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)

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

Brett_Bleess — Tue, 30 Jul 2019 18:30:15 GMT

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