Expression with two level set analysis and aggregate

[#1]

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!

[rubenmarin]

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.

[#3]

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

[#4 · Accepted solution]

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

));

[rubenmarin]

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

[#6]

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.

[rubenmarin]

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.