Dount in Set Modifiers

[#1]

Hi All,

'Avg: ' & 'R' & If( IsNull(Count({<quadrant={"1"}>}DISTINCT x)=0) ,0,  round(Sum({<quadrant={"1"}>} y)/Count({<quadrant={"1"}>}DISTINCT x))) &  'K' & Chr(10) & Chr(10) &

This is the expression I am using. I am caluclating the avgsum and the formula for that is avg sum = sum(y)/count(distinct x). And I want this for value for each quadrant. In the data set I have a column called quadrant with values 1 , 2 , 3, 4. So, I want to calculate this value for that quadrant. I want to know if the above expression is correct or not, because I am getting different results when I query the database.

Is the set modifier used : ({<quadrant={"1"}>} correct or is the syntax wrong?

[#2]

Hi udayshankark

The set modifier ({<quadrant={"1"}>} is correct. There is a function =avg() that can calculate the average, this would simplify your formula a lot and may identify the problem easier.

Erica

[#3]

udayshankark,

Looking at your problem again I see:

IsNull(Count({<quadrant={"1"}>}DISTINCT x)=0)

If there are no values of x in quadrant 1, then the count will return 0, not a null. Have you tried removing the "isnull"?

Regards,

Erica

[#4]

Hi Erica,

Thanks for the reply.

I am calculating avg but this is a different formula..so I did not use avg.

also, i am isnull to avoid divide by zero error . However I can try removing =0 in IsNull

[#5]

Erica,

Also, is there any difference between {<quadrant={1}>} and {<quadrant={"1"}>}??

[#6]

Hi Uday

Not sure about your use of isnull. counting on a field with no values will return 0, not null. If you are comparing the count, you will return true or false.

The best way to do this is just to do the condition on the count being 0.  You also don't need the distinct in the first count expression as 0 distinct x is the same as 0 x's!

If( Count({<quadrant={"1"}>}x)=0) ,0,  round(Sum({<quadrant={"1"}>} y)/Count({<quadrant={"1"}>}DISTINCT x)))

Testing in my dataset with a field that just has numeric values in it,

{<quadrant={1}>} and {<quadrant={"1"}>}

aren't any different.

Erica