Taking a distinct count of IDs with respect to suc... - Qlik Community

Report Inappropriate Content · ‎2016-04-05

Hi All,

How can we take a count of distinct IDs w.r.t successive dates... That is
If the daily values of the dates are as follows :-

Jan 1 = 10 ids
Jan 2 = 20 ids (5 ids common between jan1 and jan2)
Jan 3 = 30 ids ( 7 ids common between jan1, jan2 and jan3)
Then i should be able to remove the common ids.
Hence in the YTD trend graph it should come as
Jan 1 = 10
Jan 2 = 15 (5 common ids removed between Jan 1 and Jan 2)
Jan 3 = 23 (7 common ids removed between Jan 1, Jan 2 and Jan 3)

Count(distinct(ID)) will only give me distinct id for each date. But, I need the distinct id for each date and then compare each date with its previous date and again get a distinct id between those dates. Thereafter I need to add the result with the previous dates result.

Please let me know how can we achieve this. Please reply ASAP.
Thanks in advance.

Report Inappropriate Content · ‎2016-04-06

use Count(distinct aggr(id,id)). This will only Count the distinct ids for the date range you've chosen. see attached file.

Taking a distinct count of IDs with respect to successive dates

Count(distinct(ID)) will only give me distinct id for each date. But, I need the distinct id for each date and then compare each date with its previous date and again get a distinct id between those dates. Thereafter I need to add the result with the previous dates result.