Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join “Do More With Qlik” with Mike Tarallo to get a first-hand look at the New Authoring Experience, June 29th: REGISTER HERE
cancel
Showing results for 
Search instead for 
Did you mean: 
Dayna
Creator II
Creator II

Calculation to include the previous quarter for each column

Good Afternoon All,

I'm looking to create a report which is based on quarters, and I'm not entirely sure how to start here (other than it being set analysis!) where I want to refer to the previous quarter in each column.

So for example, I may have selected to see Q1 2020 - through to Q1 2021, and what I want to see is the following columns:

Q1 2020 + Previous (Q4 2019) 

Q2 2020 + Previous (Q1 2020)

Q3 2020 + Previous (Q2 2020)

etc...

 

I've got this for showing the last year based on selected, but I'll have multiple quarters selected (or showing everything by default). What do you suggest?

Sum({$<Year={$(=Only(Year)-1)}>} Sales)

 

Many thanks for your help!

2 Solutions

Accepted Solutions
edwin
Master
Master

one solution is to create a bridge between your fact and your calendar.  you associate a quarter in your calendar to the same quarter in your fact plus the prior quarter  

this way when you select a quarter from your calendar you automatically select two quarters in your fact and the expression is just sum(Sales):

edwin_0-1614622950596.png

 

 

View solution in original post

edwin
Master
Master

you of course will want to use your quarter nomenclature, just tweak the script that builds the bridge.  the bridge only addresses the requirement for summing current and prior quarters and is identified by the date type.  if there are other requirements where the user selects a date from the calendar and a different rule for summing by date is needed then the bridge needs to be expanded to account for that requirement 

to preserve the expression fro the quarters you now have to identify the data type from the bridge you are using:

=sum({<DateType={'CURRENT+PRIORQUARTERS'} >}Sales)

View solution in original post

7 Replies
edwin
Master
Master

one solution is to create a bridge between your fact and your calendar.  you associate a quarter in your calendar to the same quarter in your fact plus the prior quarter  

this way when you select a quarter from your calendar you automatically select two quarters in your fact and the expression is just sum(Sales):

edwin_0-1614622950596.png

 

 

edwin
Master
Master

the first chart shows the actual sales per quarter from the fact table,  the 2nd chart shows the sum of current + prior quarter per calendar quarter.  this is the validation from excel:

edwin_1-1614623277236.png

 

edwin
Master
Master

you of course will want to use your quarter nomenclature, just tweak the script that builds the bridge.  the bridge only addresses the requirement for summing current and prior quarters and is identified by the date type.  if there are other requirements where the user selects a date from the calendar and a different rule for summing by date is needed then the bridge needs to be expanded to account for that requirement 

to preserve the expression fro the quarters you now have to identify the data type from the bridge you are using:

=sum({<DateType={'CURRENT+PRIORQUARTERS'} >}Sales)

Dayna
Creator II
Creator II
Author

@edwin , thank you so much! That's perfect and really useful, you're a superstar - thanks again! 🙂 

edwin
Master
Master

yw

Dayna
Creator II
Creator II
Author

@edwin, hoping you can help me one last time...

I've replicated the solution you gave me into my report, I need to show the previous quarter value within a straight table along with other values. The field for the quarter would be the one from the data table including the sales, not the CalendarQuarter.

I only get the correct value (i.e. the sum of the quarter and previous) when I used the CalendarQuarter as my dimension. How would I get this value, but use the data table Quarter dimension? 

Many thanks,

Dayna

edwin
Master
Master

the bridge is just that - a way to get to your data from the calendar.  so if with the new business rules, you need an association between each calendar date and the date in your fact that is 1 to 1.  just add a new data type in your bridge.  and now since you have two data types you need to qualify in your expressions which data type you want to use.  if in your expression you want current + prior quarter you use datatype=CURRENT+PRIORS (i named it such so its clear to you - you can always invent your own convention) so if you wan to show just the current quarter, still use the calendar dates/quarters but this time use datatype=CURRENT.  see attached