Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

praxulla
New Contributor II

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:

   

2011201220132014
SalesvarianceSalesvarianceSalesvarianceSalesvariance
Total260-100320-403600340-20
+Region 1180-60220-202400230-10
  Shop 1100-30120-101300125-5
  Shop 280-30100-101100105-5
+Region 280-40100-201200110-10
  Shop 330-2040-1050045-5
  Shop 450-2060-1070065-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!

2 Replies
MVP
MVP

Re: Set analysis: calculating a variance based on benchmark

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.

praxulla
New Contributor II

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.

Community Browser