Solved: I want to counts the IDs by date field but due to ... - Qlik Community

JonesBeach · ‎2021-09-01

I want to counts the IDs by date field but due to applied conditions the nulls come into play and mess up the calculation.

Mock Data:

Required Result:

stevejoyce · ‎2021-09-01

ok, i still may not understand but maybe this is better...

Count(DISTINCT Total <Date> {<ID = {"=len(ID)>0"}>} ID)

Nevermind on the above. Looks like you are trying to assign new IDs to rows?

stevejoyce · ‎2021-09-01

Count({<ID = {"=len(ID)>0"}>} ID)

JonesBeach · ‎2021-09-01

Thanks so much Stevejoyce for the answer but it returns all 1s.

On contrary I think I did not do a very good job in explaining my query so let me pls try again,

Here is the mock table pls note that I am hiding all the null rows because they are out of scope.

Desired Table:

Thanks again for putting in the time and effort!

Regards,

JonesBeach

stevejoyce · ‎2021-09-01

ok, i still may not understand but maybe this is better...

Count(DISTINCT Total <Date> {<ID = {"=len(ID)>0"}>} ID)

Nevermind on the above. Looks like you are trying to assign new IDs to rows?

JonesBeach · ‎2021-09-01

Hi Steve I tried the code but it doesnt seems to work, i have made changes to the above tables for them to make more sense.

I have tried other options like:

=sum(aggr(count(distinct[EVENTID]),[CC_FACILITY]))

this does the job but adds all the null rows for IDs which gives the wrong output. Thanks!

JonesBeach · ‎2021-09-01

I apologize that line of code worked perfectly, appreciate the help!

I want to counts the IDs by date field but due to applied conditions the nulls come into play and mess up the calculation....