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

# Set Analysis function in a KPI

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?

• ###### Re: Set Analysis function in a KPI

Hi Giuseppe,

in Table You have implicit Aggr of Dim ID_CLIENT.

In KPI You need Aggr() function like

Sum(Aggr(Count(Expression),ID_CLIENT))

Regards,

Antonio

• ###### Re: Set Analysis function in a KPI

Ok, but it give me the same result.

After some tests, now the KPI works in a strange way.

If I select (from a filter) only the ID_Client that satisfy the equation, the KPI shows the exact value.

For example, if I select 5 ID_Client that has [WeightValue] = F(x), it shows 5.

But if I select 5 ID_Client that satisfy the equation, and only one ID_Client that doesn't satisfy the equation, the KPI shows me 0. It think was correct if it shows 5, not 0.

Some ideas?

• ###### Re: Set Analysis function in a KPI

Set Analysis is evaluated once per object..

I think You need If() instead of Set Analysis in Your expression.

• ###### Re: Set Analysis function in a KPI

Hi,

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

Hi,

Can you share your mock app and expected output. Tks

• ###### Re: Set Analysis function in a KPI

Sorry, but I can't share the app.. there are data that i can't show.

• ###### Re: Set Analysis function in a KPI

Try if this helps:

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 )

• ###### Re: Set Analysis function in a KPI

Nope.

Look carefully, I even used bold for the characters I've added.

• ###### Re: Set Analysis function in a KPI

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

Ok, I can try to write the function (I can't change the script) as you suggest, but there is the possibility that we will have a lot of flags so it become very hard to write all cases.

Is there another solution?

• ###### Re: Set Analysis function in a KPI

If you you are going to have several flags this is something that should definitely be addressed in the script. In the mean time the solution I suggested is your best bet.

• ###### Re: Set Analysis function in a KPI

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

Guiseppe,

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

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.