topic Re: need help in set analysis in QlikView

need help in set analysis

musketeers — Thu, 22 Jan 2015 14:11:21 GMT

Hi,

I need to get the count of distinct record id where the sum of values is 0.

like if (sum(value) = 0, count(distinct record_id)

but it is not working.

I was thinking if we can write like this:

Count({<$(=sum(Value)) = {0}>}distinct record_id)

but it is also not working.

Please help me here how we can write this situation.

Thanks

Re: need help in set analysis

datanibbler — Thu, 22 Jan 2015 14:38:50 GMT

Hi Rajneesh,

I think inside a set_expression, you can only have a "native field" (not an expression) on the left side of the "=" and an expression on the right.

I guess you have several value_fields in one record? And you want to count those where the sum is 0?

So I guess you'll have to calculate another field with the sum in the script so you can use that (but then, when you're at that, make it a binary field (1 or 0) and you can just sum it up, no need for any set_expression anymore.

HTH

Best regards,

DataNibbler

Re: need help in set analysis

Gysbert_Wassenaar — Thu, 22 Jan 2015 14:39:43 GMT

count({<record_id={'=sum(value)=0'}>}distinct record_id)