topic Re: aggr function min Date group by in QlikView

aggr function min Date group by

Wed, 27 May 2015 09:13:17 GMT

Hi,

i have some trouble to find the right solution. i´m struggling around for a while with this issue.

let´s say i have following table:

Now i want to count all distinct "type_ids" with at least one new "user_id" for given week.

Solution should look like this:

with dimension "week"

week	count type_ids with at least 1 new user_id
1	3
2	1

for week 1 there are 3 new user_ids

for week 2 only 1 new user_id (user_id = 4). the other user_ids (1,2,3) had already been in week 1, so they should not be counted in week 2.

Hope it´s clear what i´m trying to achieve.

Thanks for help.

regards

Steve

Wed, 27 May 2015 10:47:16 GMT

I think i found the solution by myself:

Count(Distinct If(Aggr(NODISTINCT Min(Date), user_id) = Date, type_id))

anbu1984 — Wed, 27 May 2015 12:09:49 GMT

If you have same type_id for different user_ids, then to get correct result, try like this

Count(Distinct If(Aggr(NODISTINCT Min(Date), user_id) = Date, user_id))