5 Replies Latest reply on Nov 20, 2018 7:24 AM by Anthony Griffiths

# Calculating Variance of Previous Quarter

Hi all,

With the attached workbook, I'm a bit stuck on working out the previous quarter's average score for each Partner and Subcontractor.

On Sheet6, if i use the lookup function, it can work if I only use the Quarter dimension but if I want to show a table with Quarter, Partner, Subcontractor I'm  not sure how to calculate the previous quarter's value for that particular Partner/Subcontractor as the lookup function can't determine which row to look at

Hope this makes sense

Any help would be greatly appreciated

• ###### 1. Re: Calculating Variance of Previous Quarter

Hi Anthony,

Find my approach as reference below

1. Previous Quarter: {fixed [Partner],[Subcontractor]:sum(if datediff('quarter',[Quarter],today())=1 then [Score] END)}

2. Current Quarter: {fixed [Partner],[Subcontractor]:sum(if datediff('quarter',[Quarter],today())=0 then [Score] END)}

3. % Delta: (Previous Quarter- Current Quarter)/Previous Quarter

PS there is no data in your dataset for Current Quarter

Regards,

Norbert

• ###### 2. Re: Calculating Variance of Previous Quarter

Hi

Thanks for the response

There's no current quarter, it needs to compare all quarters dynamically so Q4 2011 will compare with Q3 2011, Q1 2012 with Q4 2011, etc, etc

So essentially there will be a table with all the quarters, all partners, subcontractors and the end user will be able to see how their quarterly variance has changed over time.

So in the attached, on sheet 6, is the table I'm looking to see, but with an extra measure to show the variance

(AverageInPriorQuarter is the field i'm trying to calculate correctly)

• ###### 3. Re: Calculating Variance of Previous Quarter

Hi

Is there any support for this? Still stuck on it unfortunately

Kind Regards

• ###### 4. Re: Calculating Variance of Previous Quarter

Hi Anhtony,

Please check my updated approach once again in attached workbook version 10.4 located in the original thread and let me know if this is the desired functionality.

Hope it helps,

Regards,

Norbert

• ###### 5. Re: Calculating Variance of Previous Quarter

Hi,

Unfortunately not

What the users are looking for is a table with Quarter, Partner, Subcontractor (as a dimension) with avg([Normalised Score]), avg([NormalisedScorePreviousQuart]) (This would be the calculated field), variance

This will allow them to see a full view of how their variances have changed, rather than selecting one quarter at a time via a parameter