4 Replies Latest reply on Mar 8, 2017 8:41 AM by Piero Alessio

    Dynamic SUM or MAX based on FLAG

    Piero Alessio

      Dear community,

       

      I am struggeling with the following problem. I have a Data source as described below.

      It has KPI names and a Flag which indicates that this KPI is either a Kummulative (= sum of all Monthly Values for the selected date range)

      or a non-cummulative value(=get only data for the latest month available in partition).

       

      Data Source

      MonthValueKPI NameFLAG
      2016055AT
      2016066AT
      2016077AT
      2016088AT
      2016099AT
      2016055BF
      2016066BF
      2016077BF
      2016088BF
      2016099BF

       

      Target outcome

      KPI NameCalculated Field
      a35
      b9

       

      Calculated Field

      //But this here does not really work

      IF ATTR([FLAG])='Y' THEN

      (IF (ATTR({FIXED [KPI Name]: (MAX([MONTH]))}))=ATTR([MONTH]) THEN SUM([Value]) ELSE NULL END)

      ELSE

      SUM([Value])

      END

       

      Can you help me solving my problem?

       

      Thank you