Count and Distinct not working

[sagar_vij99]

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

[Anil_Babu_Samineni]

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])

[sagar_vij99]

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!

[Brett_Bleess]

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