topic Count and Distinct not working in QlikView

Count and Distinct not working

sagar_vij99 — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

i have been given a dashboard where i found below expressions:

We have a table in the dashboard and its dimension is AY2 variable.

In Expressions, for each expression we define what this AY2 is, for example for Claims Amount:

sum ({<YoA -= {0}>} -(AY2= year(LossAttachmentDate_c)) * [Claim Incurred Net&Tty_c])

Because of that [Claim Incurred Net&Tty_c] is then summarized being grouped by LossAttachmentDate ( = Accident Year). Also if somebody can explain how this expression works, it will be great.

In the table, using different AY2 definitions all works fine, but we have a problem to count the number of Claims.

To do that, we have to use functions count and distinct but then defining AY2 doesn’t work and it doesn’t count distinctive Claim Numbers well.

count ( DISTINCT {<YoA -= {0}>} -(AY2=year(LossAttachmentDate_c)) * [Claim Number_c])

Do you have any idea what we can do? Or maybe we’re doing something wrong?

Regards

Sagar

Re: Count and Distinct not working

Anil_Babu_Samineni — Wed, 01 Apr 2020 11:50:56 GMT

It is not comparing correctly, You should use some Aggregate functions like follows

count ( DISTINCT {<YoA -= {0}, AY2={"$(=year(Max(LossAttachmentDate_c)))"}>} [Claim Number_c])

Re: Count and Distinct not working

sagar_vij99 — Tue, 07 Apr 2020 10:37:21 GMT

Hi Anil,

Thank you so much for reply.

Your line of code summaries all claims and displays just the total number for all the years, even when I delete max:

AY # of Claims

2003 30428

2004 30428

2005 30428

2006 30428

30,428 is the total number. And we’re struggling to group it by Accident Year (AY2) (which is year(LossAttachmentDate_c) for that column.

Maybe it is impossible to define AY2 with count and distinct function?

Thanks again for all the help!

Re: Count and Distinct not working

Brett_Bleess — Wed, 22 Apr 2020 21:19:28 GMT

Have a look at the following Design Blog post, believe it may be what you need in relation to what Anil was saying:

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

Regards,
Brett