16 Replies Latest reply: Jul 20, 2017 2:59 PM by James Parham RSS

    Set Analysis function in a KPI

    Giuseppe De Vivo

      Hi all,


      I'm trying to understand why my expression doesn't work.

      The expression is:


      count( {$<[WeightValue] = {"=if(flag_01 = 1, 0.25, 0)+if(flag_02 = 1, 0.25, 0)+if(flag_07 = 1, 0.25, 0)"} >} ID_Client )


      In my table Client there is a column [WeightValue] that has always the value "0.25".

      In the same table, i have 3 flags (flag_01, flag_02 and flag_03) which can assume the value 0 or 1.

      I need to compare the value of the column [WeightValue] with the sum of the flags (as you can see in the expression).

      In this case, I need to check if there are some client that have [WeightValue] = 0.25.

      The KPI show me always the value 0 (so I can think that there are no Client that satisfy the equation).

      But if I create a Table as ID_Client as Dimension and a Misure trought the funcion

      if(flag_01 = 1, 0.25, 0)+if(flag_02 = 1, 0.25, 0)+if(flag_07 = 1, 0.25, 0)

      I can see that there are some client that have 0.25 as a result of the function.


      The KPI show me the correct value if I select one of ID_Client that satisfy the equation (when getSelectedCount(ID_Client) = 1 and the equation is satisfied).


      Can someone help me, please to understand wich is the problem?


      Thank you in advance.

        • Re: Set Analysis function in a KPI
          Antonio Mancini

          Hi Giuseppe,

          in Table You have implicit Aggr of Dim ID_CLIENT.

          In KPI You need Aggr() function like




          • Re: Set Analysis function in a KPI
            Devarasu R



            How about using nested if?

            count( {$<[WeightValue] = {"=if(flag_01 = 1,0.25,if(flag_02 = 1,0.25,if(flag_07 = 1,0.25,0)))"} >} ID_Client ) 

            • Re: Set Analysis function in a KPI
              James Parham

              I'm not sure I understand the problem, but it sounds like you just need a count of clientIDs where only 1 of the 3 flags is set to 1. I'm assuming that based on your if statement:


              If that's the case the easiest what to accomplish this is to just create a new field in the script.


              Here's what it would look like in the script:

              flag_01 + flag_02 + flag_07 as CountWeightFlag


              Then your set analysis would look like this:

              Count({<CountWeightFlag={1}>}ID_Client )

              If you don't have access to the script you can accomplish like this:

              count({<flag_01={1}, flag_02={0}, flag_07={0}>}ID_Client ) + count({<flag_01={0}, flag_02={1}, flag_07={0}>}ID_Client )  + count({<flag_01={0}, flag_02={0}, flag_07={1}>}ID_Client )

              • Re: Set Analysis function in a KPI
                Giuseppe De Vivo

                Ok, I think i have omitted an important aspect about my application!

                To semplify the problem, I haven't explained that the user can choose a weight that they want assign to each flag.

                I mean there is a sheet that contains combo-boxes, one for each flags, throught which the user can choose "0.25" or "0.50" or "0.75" or "1". The user can assign a different weight to each flags.

                For example, the user can assign weight 0.50 to flag_01, weight 0.75  to flag_02, and so on.

                I create a varaiable for each flag, that contain the specific weight assigned to the user to the relative flag.

                For example: vWeightFlag_01 = 0.50 , vWeightFlag_02 = 0.75, and so on.

                So, in this way, the user can change each weight when he wants, and the process is totally dynamic.


                This is the reason why i can't do it throught the script, because the choice of weight is dinamically.


                I know that each KPI can assume values 0 or 1.

                If a KPI has value 0, then the weight is 0.

                If a KPI has value 1, then the weight is that was selected by the user.


                So, the expression to define the correct weight for each flags is:

                if(flag_01 = 1, vWeightFlag_01, 0)

                if(flag_02 = 1, vWeightFlag_02, 0)

                ..and so on..

                Finally, I need to define some ranges and I need to count how many client are in each range.

                The measure is the sum of the weights of each flags.

                For the client001 the sum can be:

                if(flag_01 = 1, vWeightFlag_01, 0)  + if(flag_02 = 1, vWeightFlag_02, 0) + if(flag_03 = 1, vWeightFlag_03, 0) = 1.5.


                So, I have built my KPI to do this (I'm trying to do this).

                My KPI expression to check how many Clients have the sum = 0.25 is:


                count( distinct {$<[WeightValue] = {"=if(flag_01 = 1, vWeightFlag_01, 0)+if(flag_02 = 1, vWeightFlag_02, 0)+ if(flag_07 = 1, vWeightFlag_07, 0)"} >} ID_Client )


                The behavior of this kpi is strange.

                It shows me always ZERO, but if I select all the ID client that i know that have 0.25 as sum of flag's weight, it shows me the correct count (for example 3).


                I'm trying to understand why it works only if I make a single or multiple selection.


                Do you have any ideas?

                  • Re: Set Analysis function in a KPI
                    Michael Solomovich


                    There are contradictions between your initial post and the latest explanation, plus contradictions within this explanation itself.  Not to criticize, but to ask you to upload your application, or better a small example a similar application.  Otherwise it is not productive to continue guessing.

                    • Re: Set Analysis function in a KPI
                      James Parham

                      It's behaving that way because the KPI does not look at each row, it's an aggregation of all  Client_Ids, so it will not work until only a single selection is made. If you were to create a straight table with the Client_Id and the expression, it would work because it's able to evaluate each individual row.


                      I think it would be helpful to know what you want in the KPI, my understanding was that you just want a count where the sum of the flags weight = WeightValue. If the weight of each flag is a moving target does that mean that the field WeightValue is also a moving target? I only ask that because if the WeightValue (as you said it's always .25) is static then you would want to ignore any flags > .25 because the combo boxes other than .25 are all greater than .25.