topic Re: Set Analysis function in a KPI in Visualization and Usability

Set Analysis function in a KPI

Wed, 19 Jul 2017 09:47:49 GMT

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

antoniotiman — Wed, 19 Jul 2017 09:56:24 GMT

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

devarasu07 — Wed, 19 Jul 2017 10:03:16 GMT

Hi,

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

Wed, 19 Jul 2017 10:15:33 GMT

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

Wed, 19 Jul 2017 10:19:08 GMT

Hi - devarasu07‌ - but I need to sum the value of the flags (flag1+flag2+flag3).

The problem is not the nested if, the function works as I writed it.

I don't understand the beaviour of the KPI

Re: Set Analysis function in a KPI

antoniotiman — Wed, 19 Jul 2017 10:24:10 GMT

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

devarasu07 — Wed, 19 Jul 2017 10:27:46 GMT

Hi,

Can you share your mock app and expected output. Tks

Re: Set Analysis function in a KPI

Wed, 19 Jul 2017 10:41:54 GMT

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

Re: Set Analysis function in a KPI

Anonymous — Wed, 19 Jul 2017 11:32:38 GMT

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

Anonymous — Wed, 19 Jul 2017 11:51:36 GMT

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

Thu, 20 Jul 2017 09:21:13 GMT

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

Re: Set Analysis function in a KPI

Thu, 20 Jul 2017 09:24:11 GMT

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

Anonymous — Thu, 20 Jul 2017 11:24:50 GMT

Nope.

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

Re: Set Analysis function in a KPI

Anonymous — Thu, 20 Jul 2017 13:21:02 GMT

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

Thu, 20 Jul 2017 14:17:31 GMT

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

Anonymous — Thu, 20 Jul 2017 17:16:25 GMT

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

Anonymous — Thu, 20 Jul 2017 18:59:15 GMT

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.

topic Re: Set Analysis function in a KPI in Visualization and Usability

Set Analysis function in a KPI

Re: Set Analysis function in a KPI

Re: Set Analysis function in a KPI

Re: Set Analysis function in a KPI

Re: Set Analysis function in a KPI

Hi - devarasu07‌ - but I need to sum the value of the flags (flag1+flag2+flag3).

Re: Set Analysis function in a KPI

Re: Set Analysis function in a KPI

Re: Set Analysis function in a KPI

Re: Set Analysis function in a KPI

Re: Set Analysis function in a KPI

Re: Set Analysis function in a KPI

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

Re: Set Analysis function in a KPI

Re: Set Analysis function in a KPI

Re: Set Analysis function in a KPI

Re: Set Analysis function in a KPI

Re: Set Analysis function in a KPI

Re: Set Analysis function in a KPI

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