SUM(), COUNT() Set Analysis question

[Anonymous]

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

[Anonymous]

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

[sunny_talwar]

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?

[Anonymous]

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

[sunny_talwar]

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)

[Anonymous]

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

COUNT(DISTINCT Month)

or

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

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

[sunny_talwar]

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.

[Anonymous]

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.

[sunny_talwar]

No problem