Qlik Community

Ask a Question

New to QlikView

If you’re new to QlikView, start with this Discussion Board and get up-to-speed quickly.

Announcements
Do More with Qlik - for Beginners and Beyond, Topic: Qlik Replicate on January 21, 2PM EST. REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Creator
Creator

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

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

Labels (1)
2 Replies
Creator
Creator

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)

Digital Support
Digital Support

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.