Average rank

[Anonymous]

Hi!

I'm trying to create an average rank calculation for the best days of every product. This is what I got this far:

So I have the sales, and rank of each product for every day. So these numbers means for example that Product A was ranked 1 on 2015-09-01, 2015-09-02 and 2015-09-03 for all products those days. And that expression looks like this:

aggr(rank(Sales),Date,Product)

What I want to do now is to calculate the average rank for the 10 best days for this product, and to remove the date field as dimension.

So for Product A I only want one row like this:

Product	Average rank
Product A	2.7

And 2.7 here is calculated from:

(1+1+1+2+2+2+3+4+5+6)/10 = 2.7

How do I need to change my expression to be able to get this as a result?

Thanks in advance!

[Anonymous]

I think mine is correct, yours seems to rank the days within each product. But I wan't to know the rank for all products for each day.

So in theory, one product could have rank 1 for all 10 days I have. But your logic gives every product a rank of 1-10 for the available days.

[Anonymous]

Thanks alot, that one actually works!

I do think I need to do it in the expression though, and I've managed to do it like this:

avg(

if(aggr(rank(-aggr(rank(Sales),Date,Product)),Product,Date)<6,

aggr(rank(Sales),Date,Product),NULL()))

Which actually seems to be working, but I dot get some performance issues with all the ranks, ifs and aggregations.