3 Replies Latest reply: Oct 6, 2010 5:41 PM by Karl Pover RSS

    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