topic Expression with two level set analysis and aggregate in QlikView

Expression with two level set analysis and aggregate

Tue, 03 Feb 2015 14:18:43 GMT

Hi all,

I need to perform a query like the following but it's not working. Is it possible?

Count(DISTINCT {1 <event.month={"$(=Month(Today()))"}>}
Aggr(MIN({<event.name={"sale"}>} event.date),userId)

)

The data looks like this:

userId event.date event.month event.name

1 20141230 12 question

2 20141229 12 question

1 20150130 1 sale

1 20150202 1 sale

2 20150201 1 sale

2 20150202 1 sale

In this case, first I would like to aggregate userId and min(event.date) where event.name='sale'.

And then, count distinct user where the first sales date occurred in the current month.

The result would be 1.

Thanks!

Re: Expression with two level set analysis and aggregate

rubenmarin — Tue, 03 Feb 2015 14:40:24 GMT

Hi, If event.month is a number you need to filter by that number, anyway, I think this expression is counting the different event.date.

Month(Today()) will return: feb

Num(Month(Today())) will return: 2

I think you need to check year&Month, in this case you can use:

=Count(DISTINCT Aggr(If(Left(Min(event.date), 6)=Year(Today()) & Num(Month(Today()), '00'), userId), userId))

Hope this helps.

Re: Expression with two level set analysis and aggregate

Tue, 03 Feb 2015 15:34:25 GMT

Thanks Ruben. What about the event name? In that case maybe the min date is from another event name.

Re: Expression with two level set analysis and aggregate

Tue, 03 Feb 2015 15:36:51 GMT

I found a solution, but I would like to know why the other approach doesn't work.

Count(IF(

Aggr(MIN({<event.name={"sale"}>} event.month),userId)=NUM(MONTH(TODAY())),userId

));

Re: Expression with two level set analysis and aggregate

rubenmarin — Tue, 03 Feb 2015 15:52:55 GMT

Yep, i forgot the event.name.

The other approach has the thing about the month, qv was filtering Month='feb' instead of Month=2.

Also was counting distinct Min(event.date) by UserId, so userId1 will be counted (by 20150202), and if it was another UserId with a Min(event.date) alredy counted it will not count, in example if there was an UserId3 with his first sale on 20150201, is the same event.date than UserId1

Re: Expression with two level set analysis and aggregate

Tue, 03 Feb 2015 16:13:34 GMT

You are right about the distinct, but it's still not working:

Count({1 <event.month={"$(=num(Month(Today())))"}>}
Aggr(MIN({<event.name={"sale"}>} event.date),userId)
)

The event.month in <event.month={"$(=num(Month(Today())))"}> is operating on the MIN of the aggregate or outside? Which set analysis come first?

The year is important, but there is no overlap in the data, so I m not taking into account for the moment.

Re: Expression with two level set analysis and aggregate

rubenmarin — Tue, 03 Feb 2015 16:27:24 GMT

Hi, not 100% sure but <event.month={"$(=num(Month(Today())))"}> is apllied to the Aggr().

it's counting the min(event.date) with event.month=2, so UserId1 will be counted. If you filter the data where event.month=2, it's not discarding the UserId with sales on previous months.