Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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])
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!
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