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

    Set analysis: calculating a variance based on benchmark

    Gerhard de Beer

      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!