1 Reply Latest reply on Jun 30, 2018 11:58 PM by Shinichiro Murakami

    Compare rows based on dimension and apply logic for units separately.

    Swetha J

      Hi everyone,

       

      I have a table that looks like the following:

       

      I have created 2 calculated field that meets the following requirement(partially):

       

       

      Cal field 1: {FIXED [Id],[State],[City],[Product]:MAX([Kpi])}

       

      Cal field 2:   IF (ATTR([Cal]) = LOOKUP(ATTR([Cal]),1) OR

                     ATTR([Cal]) = LOOKUP(ATTR([Cal]),-1))

                     THEN 'Good'

                     ELSE 'Bad'

                     END

       

       

      My logic only works when row 1(Cal) = row 2 (Cal)   as below output

       

       

       

      But trying to achieve Condition 1: IF Unit = #    Cal value of row 1 and row 2 (difference) <= 10 then 'Good' else 'Bad'

                                                      Condition 2: IF Unit = %   Cal value of row 1 and row 2 (difference) <= 5 then 'Good' else 'Bad'

       

       

       

       

      The resulting table would have the following:

       

       

      Thanks,

      SwethaJ