Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Count and Distinct not working

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

3 Replies
Highlighted

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

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator
Creator

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!

Digital Support
Digital Support

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

To help users find verified answers, please do not forget to use the "Accept as Solution" button on any post(s) that helped you resolve your problem or question.
I now work a compressed schedule, Tuesday, Wednesday and Thursday, so those will be the days I will reply to any follow-up posts.