topic Re: Set Analysis Sum Distinct in QlikView

Set Analysis Sum Distinct

Fri, 20 Dec 2013 14:57:53 GMT

I am trying to sum the difference between two date values for unique combinations of Name, Date1, and Date2

I have data of the format:

Name	Date1	Date2
Chuck	1/1/12	1/3/12
Sam	1/1/12	1/3/12
Sam	1/3/12	1/5/12
Sam	1/3/12	1/5/12
Sam	1/3/12	1/5/12

And I want to sum Date2 - Date1 for unique combinations. In this case, Chuck would get 2, the first line of Sam would get 2, and the next 3 lines of Sam are all identical so would count once and be 2 for a total of 6.

Currently all I can get is 2+2+2+2+2=10

Anybody know how to code this?

Re: Set Analysis Sum Distinct

swuehl — Fri, 20 Dec 2013 15:04:02 GMT

Maybe like

=sum( aggr( Date2-Date1, Name, Date2, Date1))

Re: Set Analysis Sum Distinct

maxgro — Fri, 20 Dec 2013 15:19:05 GMT

swuehl,

perhaps the aggr with nodistinct?

Re: Set Analysis Sum Distinct

Mon, 30 Dec 2013 19:42:59 GMT

Worked great, and my coworker thought I was a genius for coding it this way!