Calculation to include the previous quarter for each column

[Dayna]

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!

[edwin · Accepted solution]

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]

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 · Accepted solution]

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]

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

[edwin]

yw

[Dayna]

@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]

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

[vikasmahajan]

data:
load date(today()-iterno()+1) as Date, QuarterName(date(today()-iterno()+1)) as Quarter, floor(rand()*1000) as Sales
while iterno()<=365;
load 1 AutoGenerate(1);

calendar: load distinct Quarter as CalendarQuarter, Date as CalendarDate Resident data;

tmpQuarters: load distinct CalendarQuarter Resident calendar;

inner join (calendar) load CalendarQuarter, RowNo()-1 as CalendarQuarterIndex Resident tmpQuarters;

drop table tmpQuarters;

NoConcatenate tmpBridge: load distinct CalendarQuarterIndex, CalendarDate Resident calendar;
inner join (tmpBridge) load CalendarQuarterIndex as QuarterIndex, CalendarDate as Date Resident tmpBridge;

NoConcatenate Bridge: load CalendarDate, Date, 'CURRENT+PRIORQUARTERS' as DateType resident tmpBridge
where QuarterIndex=CalendarQuarterIndex
or QuarterIndex=CalendarQuarterIndex+1;

drop table tmpBridge;