1 Reply Latest reply: Jan 24, 2018 4:05 PM by Bill Markham RSS

    Calculated dimension for year comparaison

    Bruno Dumas

      Hi,

      To start with I defined a table with the dimension RSS_ANNEE in column:

       

      2017201620152014
      100303540

       

      Now I want to replace RSS_ANNEE by a calculated dimension to show the greatest and smallest selected years in the RSS_ANNEE dimension, and the delta between the two years.

      Expected result if 2017, 2016, 2015 are selected in RSS_ANNEE:

      20172015Delta
      1003565

       

      To achieve that I declare a Master Dimension MyDim =ValueList($(=Max( RSS_ANNEE)),$(=Min( RSS_ANNEE )),$(=Max( RSS_ANNEE))-$(=Min( RSS_ANNEE )))


      I get (with 2017, 2016, 2015 selected in RSS_ANNEE):

      201720152
      165165165

      The result shows the agregation of the 3 selected years on the 3 columns of MyDim. I understand there is no relationship between MyDim and RSS_ANNEE and MyDim formula was only used to calcule the columns titles.


      I could get the expected result by defining 3 measures, but the real table has 3 sub-columns for each year, so I would need to define 9 measures?

      That's why I would rather define a calculated dimension if this is possible.

      Thanks!