5 Replies Latest reply on Aug 4, 2016 9:31 PM by Shinichiro Murakami

    Combine 2 calculated field filters together (one is aggregated one is not)


      I have a simple forecasting dashboard with last year actuals, this year actuals and this year forecast.

      I have 2 calculated fields which i want to use as filters.

      The first calculated field allows me to only keep forecast values for 2016 since im not interested in forecast values of 2015. Its the second pill in the row shelf.

      2016 forecast

      if year([Date]) = 2016

      then [Forecast]



      The second calculated field allows me to display only the last 2 forecasted values. So anything that the actuals covered already, i do not want to display.

      The calculated field works but i dont know how to integrate it into the chart.

      last 2 forecast

      IF [Index]>(WINDOW_MAX([Index]) -[param_index])

      THEN ATTR([Forecast])


      Screen Shot 2016-08-03 at 5.01.42 PM.png


      ultimately, i want to see something like this where the blue  last n forecast values and green is actuals. so there is no overlap. there is some here because i was testing, but you get the point.

      Screen Shot 2016-08-03 at 5.13.11 PM.png


      How do i combine the above 2 calculated fields together such that i can just use the combined calculation as the second pill on the row shelf?

      i tried:

      if year([Date]) = 2016

      and [Index]>(WINDOW_MAX([Index]) -[param_index])

      THEN ATTR([Forecast])


      but i got the error that i cannot combine an aggregated value with a non-aggregated one.

      Workbook attached.