Skip to main content
Announcements
Marching toward a simplified navigation! READ ON
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Avoiding Duplicate Counts in a Cumulative Chart - Min Date Problem

Hi All,

I have a bit of a problem. Basically I have two tables: Issues and DPFs- the relationship between the tables is many to many. I am creating a chart which cumulatively counts Issues by their Dates. I would now like to add DPFs to the chart as well, this table does not have any dates though, and so must use the Issue Date through the relationship. The problem I am having is this, lets say 3 issues with different dates (say may, june and july) relate to the same 1 DPF - this DPF is being counted 3 times, once each in may june and july to give a cumulative count of 3 when the count should just be 1. To resolve this we agreed with the client we will only count the DPF when it first occurrs ie with the min issue date.

I have come up with the following but I don't think it is working:

COUNT(IF(Issue_DATE_SUBMITTED=(aggr(min(ssue_DATE_SUBMITTED),DPF_ID)),DPF_ID))

Can somebody please tell me what I am doing wrong?? Also, just out of curiosity since I am relatively new to qlik- can set analysis be used in this case at all??

Thanks in advance for your help!

Julia

3 Replies
pover
Luminary Alumni
Luminary Alumni

Julia,

See the attached file for help. It depends on what your report looks like, but you'll need to compare the dimension value with the expression. For example if MonthYear is your dimension,

COUNT(IF(MonthYear=(aggr(monthname(min(Issue_DATE_SUBMITTED)),DPF_ID)),DPF_ID))

Regards.

Not applicable
Author

The Issue Date Submitted is my dimension....but this still doesn't work out for me. 😞

pover
Luminary Alumni
Luminary Alumni

Can you upload a reduced version of your QlikView?