topic Re: Set Analysis issue with the Count function in QlikView

Set Analysis issue with the Count function

Mon, 03 Feb 2014 22:28:47 GMT

Hi all

I'm running into trouble with set analysis and the count function, and hoping the group can help. Basically, I have a formula that is calculating a rate, and I have a flag that indicates weather or not a customer has done a certain activity. That flag is 1 or 0. The rate takes a count of distinct customers that have done this action, and divides it by the total distinct customers in the population.

I am using set analysis to do this, however when I filter down the numerator (which is the count of people who did an action) doesn't seem to change, while the denominator does.

For example, if I have 100 people and 40 took action, my rate is 40%. If I select 0 for the activity flag, the total should be 0/60 = 0%. Instead I'm getting 40/60.

Here is the code I'm using. Is there an operator I'm missing here?

count({$<Flag = {'1'}>} distinct(ID))

This code returns the same number no matter what my selections are, even if I select Flag = 0

Re: Set Analysis issue with the Count function

Jason_Michaelides — Mon, 03 Feb 2014 23:10:54 GMT

Hi Ken,

This is correct behaviour. The Flag={1} in your set analysis is overriding your Flag=0 selection, so it wouldn't change!

Maybe you could duplicate your Flag field in the script, then use on of them in the set analysis and the other in the selection....

Hope this helps,

Jason

Re: Set Analysis issue with the Count function

swuehl — Mon, 03 Feb 2014 23:22:30 GMT

Try maybe the intersection operator *:

count({$<Flag *= {'1'}>} distinct(ID))

Re: Set Analysis issue with the Count function

Tue, 04 Feb 2014 00:09:50 GMT

Hi,

try to use this expression for calculate the rate:

Num(count(distinct(ID))

count({$<Flag = >} distinct(ID)) '#.##0%')

so if you select Flag 1, you have the rate of Active else of inactive.

Hope this helps.

Re: Set Analysis issue with the Count function

Tue, 04 Feb 2014 00:29:39 GMT

So doing this works, Im just not sure why and what the implications are for the rest of the data. Can you explain?

Re: Set Analysis issue with the Count function

Tue, 04 Feb 2014 05:40:23 GMT

Basically u wants % use the following express

Num((Count ({$<Flag = >} distinct (ID))

/count(total Distinct ID)),’##.##%’)