Avoiding Duplicate Counts in a Cumulative Chart - Min Date Problem

[#1]

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

[pover]

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.

[#3]

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

[pover]

Can you upload a reduced version of your QlikView?