2 Replies Latest reply: Mar 15, 2016 9:12 PM by Gerhard de Beer

# Set analysis: calculating a variance based on benchmark

I have a case where I want to calculate a variance of a measure (say, sum(Sales) ), for various reporting years for a particular benchmark year.

The benchmark year is selected by the user via a filter. I am displaying the data in a pivot table, for example:

 2011 2012 2013 2014 Sales variance Sales variance Sales variance Sales variance Total 260 -100 320 -40 360 0 340 -20 +Region 1 180 -60 220 -20 240 0 230 -10 Shop 1 100 -30 120 -10 130 0 125 -5 Shop 2 80 -30 100 -10 110 0 105 -5 +Region 2 80 -40 100 -20 120 0 110 -10 Shop 3 30 -20 40 -10 50 0 45 -5 Shop 4 50 -20 60 -10 70 0 65 -5

The user has selected 2013 as the benchmark year. The sum of sales for each year must then be subtracted from the 2013 year.

I have created the following set expression for the variance:

sum(Sales)-

sum(aggr(sum({\$<[Reporting Year]={\$(=getfieldselections([Benchmark Reporting Year]))}>} [Sales]),[Region],[Shop],[Reporting Year]))

However, this only result in the 2013 reporting year to be correctly displayed, the other years shows incorrectly.

Any help with this would be appreciated!

• ###### Re: Set analysis: calculating a variance based on benchmark

sum(Sales)-

sum({\$<[Reporting Year]={\$(=getfieldselections([Benchmark Reporting Year]))}>}

aggr(sum({\$<[Reporting Year]={\$(=getfieldselections([Benchmark Reporting Year]))}>} [Sales]),[Region],[Shop],[Reporting Year]))

Set Analysis in the Aggr function

If this doesn't help, please post a small sample QVW.

• ###### Re: Set analysis: calculating a variance based on benchmark

Unfortunately this did not work. However, I am getting better luck with the following:

Sum([Sales])-

Sum({\$<[Reporting Year]={\$(=max([Benchmark Reporting Year]))}>}TOTAL <[Region],[Shop]> [Sales])

At the lowest level in the pivot table, this is showing the correct variance based on the benchmark year. However, when rolling up to subtotal of Region and total of all regions, it is not working (due to the fact that TOTAL specifies both dimensions).

Is there a way to get both most granular rows as well as the subtotals to show the variance from the benchmark?

I attach a sample qvf.