variable in Set analysis

[#1]

ID     Limit_1     Limit_2

-------------------------------------
a     100           100
b      200           200
c      300           100
d      400           200

Using the above data, I ve created a variable in the front end as follows , diff=Limit_1-Limit_2

Here  i want to count IDs having the diff>0 using set analysis using the below expression.

=count( {$<'$(diff)' = {“>0”}>} ID )

But the above one is not working .Can anyone correct it.

[christian77]

Try this:

count(distinct if(Limit_1>Limit_2, ID))

[pat_agen]

hi,

I think your issue is being caused by set analysis needing a field to be used on the left side of the "=" sign when doing a set modification.

On the right hand side you can use your variable just as you would if doing a search. Something like this:

count({<ID={'=if(($(vDiff))>0,1)'}>} distinct ID)

[#4]

I had given the small part of a very big expression which should be done by using set analysis only

So solution using set anaylysis..???

[christian77]

Try to do Limit_1-Limit_2 as Difference, in the script.

You can do --> Field={'<=$(Variable)'}

but i´m not sure of Limit_1-Limit_2 = {'>=0'}

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

You can use advanced search in the set analysis as well, kind of

{< Limit1 = {"=(Limit1 - Limit2) > 0"} >}

Take the following dummy data

Data:
LOAD * INLINE [
ID, Limit1, Limit2
A, 200, 200
B, 300, 400
C, 100, 50
D, 300, 200
];

And in a new pivot table set ID as dimension and the following as expression

Count({< ID = {"=(Limit1 - Limit2) > 0"} >} ID)

Although that "Diff" doesn't exist in the script, using the above the chart will display C (100-50) and D (300-200). Obviously, if this can be calculated in the script, and creating a flag with values 0 for false and 1 for true, and them summing them, it will be faster.

Hope that helps.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[christian77]

Cool! Miguel Angel. Muy Bien.

[#8]

Hi Miguel,

Thanks ..This is the expected one..

Regards,

Sankar