7 Replies Latest reply on Sep 8, 2016 3:45 AM by Ganesh Vijay Kumar

    If Else calculated field with Blending Data

    Ganesh Vijay Kumar

      Hello Experts,

       

      I am trying to calculate target achieved or not using if else function but not successful.

       

      I have two data sources:

       

      Data source 1 : Perfect Orders

       

      Dimension used : TYPE

       

      Measure used : Overall Orders

      Formula used to calculated Perfect Order%

      1-(SUM(ZN(if [TYPE]<>"OVERALL ORDERS" then ([COUNT_SCH_LINE]) end ))

      /

      SUM(ZN(if [TYPE]="OVERALL ORDERS" then ([COUNT_SCH_LINE]) end )))

       

      Output will be as displayed below.

       

      Initially I had hard corded the target with the below mentioned formula. It was working well for me.

      IF

      [Perfect Orders] >= 0.9649

      THEN

      AVG(1)

      ELSE

      AVG(0)

      END

       

      Now, the problem is I have different targets for different region in an excel file. Finding it little difficult to write the formula by blending "Region". Attached is the excel file for your reference.

       

      Now I tried the below mentioned formula, its not working any help in this regard is deeply appreciated!

       

      IF ATTR([PM Target (Targets)].[KPI Name L2])="Perfect Order %"

      AND

      [Perfect Orders] >=1-SUM([PM Target (Targets)].[MET])

      THEN

      AVG(1)

      ELSE

      AVG(0)

      END

       

      Looking forward for positive response!

       

      Thanks,

      Ganesh