Set analysis: calculating a variance based on benchmark

[gdebeer]

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!

[swuehl]

Try adding the set analysis to your outer aggregation, too:

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.

[gdebeer]

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.