7 Replies Latest reply: Jun 7, 2011 9:23 AM by Ganesh T

# variable in Set analysis

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.

• ###### variable in Set analysis

Try this:

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

• ###### variable in Set analysis

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..???

• ###### variable in Set analysis

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'}

• ###### Re: variable in Set analysis

Hi,

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

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

Take the following dummy data

```Data:
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

• ###### Re: variable in Set analysis

Cool! Miguel Angel. Muy Bien.

• ###### variable in Set analysis

Hi Miguel,

Thanks ..This is the expected one..

Regards,

Sankar

• ###### variable in Set analysis

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)