# QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
cancel
Showing results for
Did you mean:
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)

2 Solutions

Accepted Solutions
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):

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)

7 Replies
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):

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:

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)

Creator II
Author

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

Master

yw

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

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