topic Re: SUM(), COUNT() Set Analysis question in QlikView

SUM(), COUNT() Set Analysis question

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

I have a question about using aggregation functions in QV in set analysis.

Suppose I have this sample dataset.

Region	Month	Total
East	Jul		1
East	Aug		2
East	Jul		3
South	Jul		4

and say I have this expression:

SUM({$<Region={'East'}>}Total) / COUNT(DISTINCT Month)

This expression has no meaning other than to ask the question. The question is this: when the COUNT() function executes,

will it include Region=South? No matter what the Region is, the count should always be a "1" because we are doing a

count distinct. What I am aiming at is when you have conditions (WHERE Region = ??), do you have to specify the

same condition in the COUNT() function as you do in the SUM()? If so, how do you write the COUNT() function?

Thanks

Re: SUM(), COUNT() Set Analysis question

Anonymous — Thu, 22 Dec 2016 16:28:21 GMT

Correction: the distinct count on the "Month" column where Region=East is 2 and not 1.

Re: SUM(), COUNT() Set Analysis question

sunny_talwar — Thu, 22 Dec 2016 16:28:43 GMT

Right now COUNT(DISTINCT Month) will give you 2 because you have 2 months in total. Not sure why you would expect to see 1?

Re: SUM(), COUNT() Set Analysis question

Anonymous — Thu, 22 Dec 2016 16:29:51 GMT

Yes, you're right and I did correct myself.

Re: SUM(), COUNT() Set Analysis question

sunny_talwar — Thu, 22 Dec 2016 16:29:53 GMT

Unless you are using this in a chart with Region as dimension, I would add the set analysis filter to the denominator also

Sum({$<Region={'East'}>}Total) / Count({$<Region={'East'}>} DISTINCT Month)

Re: SUM(), COUNT() Set Analysis question

Anonymous — Thu, 22 Dec 2016 16:36:09 GMT

Yes, I'm using the "Region" column as a dimension. Is that why it has no effect whether I use

COUNT(DISTINCT Month)

Count({$<Region={'East'}>} DISTINCT Month)

My result doesn't seem to change and that's why I ask the question.

Re: SUM(), COUNT() Set Analysis question

sunny_talwar — Thu, 22 Dec 2016 16:38:40 GMT

Yes, because your numerator is zero when Region is equal to South. So, no matter you divide by 0 or 100 the output for the row where Region = 'South' will always be 0.

Re: SUM(), COUNT() Set Analysis question

Anonymous — Thu, 22 Dec 2016 17:01:44 GMT

It's probably best to include the set analysis filter in my COUNT() just so i can be sure i'm only including the rows I want to include. Thanks for your feedbacks.

Re: SUM(), COUNT() Set Analysis question

sunny_talwar — Thu, 22 Dec 2016 17:07:59 GMT

No problem