Set Analysis function in a KPI

[#1]

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.

[#2]

Hi mov you writed the same function that I posted in my discussion.
Isn't it?

[#3]

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?

[Anonymous]

Nope.

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

[Anonymous]

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.

[#6]

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?

[Anonymous]

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.

[Anonymous]

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.