topic Re: Count using Set analysis in chart expression in QlikView

Count using Set analysis in chart expression

Fri, 20 Sep 2013 12:07:12 GMT

Hi Experts,

I want to get the count of distinct encounterid where the paid amount of that encounterid is less then or equal to zero.

I tried the following expressions i cant get the result

=Count({<EncounterID ={"=$(=Sum(PaidAmount)<=0)"}>}DISTINCT EncounterID)--Zero value is return

=Count({<EncounterID={"=(Sum(PaidAmount))<=0"}>} DISTINCT EncounterID)--Some wrong value is returned

i use the dimension as monthyear.

Can any one help me to come out of this problem

Thanks in Advance

Regards,

S.Amuthabharathi

Re: Count using Set analysis in chart expression

swuehl — Fri, 20 Sep 2013 12:23:34 GMT

I assume you want your set expression to consider your chart dimension, right? This will not work, please check

Evaluating "sets" in the context of a dimension

Re: Count using Set analysis in chart expression

somenathroy — Fri, 20 Sep 2013 12:26:54 GMT

Try with Aggr function as below:

If(Aggr(Sum(PaidAmount),EncounterID) <= 0, count(DISTINCT EncounterID))

Regards,

Som

Re: Count using Set analysis in chart expression

Fri, 20 Sep 2013 20:04:01 GMT

=Count({<PaidAmount={"=$(=Sum(PaidAmount)<=0)"}>}DISTINCT EncounterID)

Re: Count using Set analysis in chart expression

Clever_Anjos — Fri, 20 Sep 2013 20:27:45 GMT

Try

Count({<EncounterID={"=Sum(PaidAmount)<=0"}>} DISTINCT EncounterID)

Re: Count using Set analysis in chart expression

Sat, 21 Sep 2013 06:01:58 GMT

i tried this too but i can get the answer only -

Re: Count using Set analysis in chart expression

Thu, 04 Sep 2014 07:10:15 GMT

I know this post has been open for a while - but I had the same issue and finally got it to work with the below expression. Perhaps it can help the next user

=COUNT(DISTINCT if(aggr(sum(PaidAmount),EncounterID) <= 0, EncounterID))

Re: Count using Set analysis in chart expression

DutchHans — Thu, 28 Feb 2019 11:54:30 GMT

This construct also worked for me.

I had to compare a date field to my master calendar like so:

=COUNT(DISTINCT if(Einddatum >= MonthEnd(Caldatum), Number))

But is is super slow, so I need something else. Any idea's?