topic Re: Dount in Set Modifiers in QlikView

Dount in Set Modifiers

Sun, 01 Jul 2012 15:53:33 GMT

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?

Re: Dount in Set Modifiers

Sun, 01 Jul 2012 16:04:35 GMT

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

Re: Dount in Set Modifiers

Sun, 01 Jul 2012 16:05:55 GMT

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

Re: Dount in Set Modifiers

Sun, 01 Jul 2012 16:08:12 GMT

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

Re: Dount in Set Modifiers

Sun, 01 Jul 2012 16:10:51 GMT

Erica,

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

Re: Dount in Set Modifiers

Mon, 02 Jul 2012 19:55:36 GMT

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