topic variable in Set analysis in QlikView

variable in Set analysis

Mon, 06 Jun 2011 11:37:00 GMT

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

christian77 — Mon, 06 Jun 2011 11:46:13 GMT

Try this:

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

variable in Set analysis

pat_agen — Mon, 06 Jun 2011 11:56:39 GMT

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)

variable in Set analysis

Mon, 06 Jun 2011 12:00:21 GMT

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

christian77 — Mon, 06 Jun 2011 12:17:56 GMT

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

Miguel_Angel_Baeyens — Mon, 06 Jun 2011 12:34:20 GMT

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

Re: variable in Set analysis

christian77 — Mon, 06 Jun 2011 13:13:57 GMT

Cool! Miguel Angel. Muy Bien.

variable in Set analysis

Tue, 07 Jun 2011 13:23:34 GMT

Hi Miguel,

Thanks ..This is the expected one..

Regards,

Sankar