topic Avoiding Duplicate Counts in a Cumulative Chart - Min Date Problem in QlikView

Avoiding Duplicate Counts in a Cumulative Chart - Min Date Problem

Tue, 05 Oct 2010 16:00:02 GMT

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

Avoiding Duplicate Counts in a Cumulative Chart - Min Date Problem

pover — Tue, 05 Oct 2010 22:18:19 GMT

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.

Avoiding Duplicate Counts in a Cumulative Chart - Min Date Problem

Wed, 06 Oct 2010 19:55:01 GMT

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

Avoiding Duplicate Counts in a Cumulative Chart - Min Date Problem

pover — Wed, 06 Oct 2010 21:41:27 GMT

Can you upload a reduced version of your QlikView?