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!

[MK_QSL]

Provide sample data to work.. or sample application..

[Anonymous]

Here's both a QVW-file and a spreadsheet with data.

Note that it is not the exact same data as in my previous post, but the format is the same.

Since I only included 10 days in the data, we can take average rank of top 5 days.

So Product A in my example should generate (1+1+1+2+2)/5 = 1.4

[MK_QSL]

Sorry Kim but I am still not getting how you got 1+1+1+2+2.

Can you provide the logic behind this?

[Anonymous]

If you're looking at Product A and the ranks, you can see that it's been ranked at #1 for 3 days, and #2 for 2 days, right? So Product A has been the best seller for 3 of the days, among all products for those days.

So if I want to calculate the average rank for the top 5 highest ranked days for Product A, I would take (1+1+1+2+2)/5.

Does that make sense?

[MK_QSL]

Yes... Let me work on this now...!

Is that OK if we can have solution at script level?

[Anonymous]

Thanks!

I'd rather have it in the expression since it can depend different dimensions/selections. But if you find a solution in the scripts that can work as well!

[Anonymous]

If it helps, I've manage to create an expression that gives me the average rank for the 5 days with highest sales for the product, like this:

avg(

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

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

But I don't want the average rank for the top 5 selling days, I want the average rank for the top 5 days with the highest rank. But it's close

[MK_QSL · Accepted solution]

CHECK !

[Kushal_Chawda]

Is your ranking is correct?

I am getting different ranking. I am not sure if you are using any other logic