1 Reply Latest reply on Apr 15, 2016 2:00 PM by diego.medrano

    Calculated within a dimension

    Alexis Jensen

      Hi All,


      I have a data set where I track actual volumes versus multiple forecasts over time.  In order to be able to present multiple of these on the same graph, I have set up a dimension called data type (eg: FY16 actual, Feb Forecast etc).  I want to be able to have a calculation that will find the difference between the actual and one of the forecasts (set by filter or within the calculation).  Is this possible when all of the data sets are within a single dimension? Or:


      • Do I need to transform the data so each data set is an individual dimension / measure that can be used in the calculated field? or
      • Do I even need to go to the extent of in data preparation create a field that is a calculation of the difference (eg: Actual vs Feb Fcst)?


      Secondly, with whatever method would work, is it possible to have a filter or parameter that allows the user in a dashboard to select the data sets within the calculation?


      I have thought about creating a parameter to select which forecast the user wants in the calculation and then include that calculated field in the table and difference calculation.  however but don't know how how to get that to work.  I get an error saying you can't use boolean logic in the calculated field.  My example is as follows:


      I have a Parameter - Forecast for KPI.  Then I tried creating a calculated field:


      CASE [Forecast for KPI]

      WHEN "F16_(Feb 16)" THEN [Data Type] = "F16_(Feb 16)"

      WHEN "F16_(Mar 16)" THEN [Data Type] = "F16_(Mar 16)"



      This fails for an error.


      I have a dimension called DATA TYPE and I want to use parameters or filters to have 2 user selected values in a table (see below) with a third colum that represents  A - B.  Eg: F16_Actuals - F16_(Mar 16)




      In simple terms I want to have a measure that is the difference between DATA TYPE Value A - DATA TYPE value B


      I appreciate any insight that can be offered.