Count offers per rank and date

[jbrodmann]

Hello, i have here a tricky one.

At first i have an offer table. The relevant columns and example data is shown in the following table:

offer_id	product_id	offer_date	merchant	price
1	1	01/01/2019	A	5.00
2	1	01/01/2019	B	7.00
3	1	01/01/2019	C	4.00
4	1	01/01/2019	OWN_CLIENT	6.00
5	2	01/01/2019	A	20.00
6	2	01/01/2019	B	19.00
7	2	01/01/2019	C	21.00
8	2	01/01/2019	OWN_CLIENT	18.00
9	1	01/02/2019	A	6.00
10	1	01/02/2019	B	7.00
11	1	01/02/2019	C	4.00
12	1	01/02/2019	OWN_CLIENT	8.00
13	2	01/02/2019	A	19.00
14	2	01/02/2019	B	20.00
15	2	01/02/2019	C	21.00
16	2	01/02/2019	OWN_CLIENT	22.00

I want to get my (OWN_CLIENT) rankings for each day. The result would look like:

Date	Rank	offer count
01/01/2019	1	1
01/01/2019	2	1
01/02/2019	4	2

In the next step all ranks above 5 should be grouped as Rank '6+'.

My solution so far:

Dimension 1: 

offer_date

Dimension 2:

=aggr( {1<merchant={'OWN_CLIENT'}>} // first aggr to get the group by product and date
	ONLY(
        aggr( // second aggr to rank all offers per product and date
            {$ + 1<merchant={'OWN_CLIENT'}>}
            if (rank(-min(price),1,1) <= 5, // group ranks above 5 to 6+
                rank(-min(price),1,1)
                , Dual('6+', 6)),
            offer_date,
            product_id,
            offer_id
        )
    ), 
    offer_date,
    product_id
)

Measure:

Count(distinct product_id)

This works so far and is giving me all correct rankings. 

The disadvantage is, this solution is very slow. Maybe someone has a better, not so awkward solution for this szenario?

Greetings, Jens

[sunny_talwar · Accepted solution]

The only thing you can really simplify without modifying the script is to simplify your if statement to this

=Aggr(
	Only({1<merchant={'OWN_CLIENT'}>}
        Aggr(
            RangeMin(Rank(-Min({$ + 1<merchant = {'OWN_CLIENT'}>} price), 1, 1), Dual('6+', 6))
            , offer_date, product_id, offer_id)
    )
, offer_date, product_id)

Otherwise, another option is to calculate the Rank part in the script as it seems that it doesn't have to change based on selection of any sorts because you use 1 in your expression's set analysis.

[jbrodmann]

Thank you for the fast reply.

The RangeMin function is great, it makes the whole thing more readable.

Unfortunately this have to react on filters (the inner aggregation) and pre calculation in the load script is not possible.

There are 500+ merchants and 50,000+ offer, it seems calculating rankings for this amount of data and each day is not very fast.

My second attempt is to count all offers that are cheaper than my per product:

=aggr(
    RangeMin(
     	count(distinct if(price<own_price, offer_id)) + 1
        , Dual('6+', 6)
    ),
    offer_date,
    product_id)

That is giving me the same values like the rank function, but a bit faster.

It seams the amount of data is the problem here.