topic Re: Average rank in QlikView

Average rank

Anonymous — Thu, 13 Aug 2015 12:38:04 GMT

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!

Re: Average rank

MK_QSL — Thu, 13 Aug 2015 12:52:34 GMT

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

Re: Average rank

Anonymous — Thu, 13 Aug 2015 13:10:58 GMT

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

Re: Average rank

MK_QSL — Thu, 13 Aug 2015 13:15:05 GMT

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

Can you provide the logic behind this?

Re: Average rank

Anonymous — Thu, 13 Aug 2015 13:22:01 GMT

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?

Re: Average rank

MK_QSL — Thu, 13 Aug 2015 13:28:43 GMT

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

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

Re: Average rank

Anonymous — Thu, 13 Aug 2015 13:31:56 GMT

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!

Re: Average rank

Anonymous — Thu, 13 Aug 2015 13:40:05 GMT

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

Re: Average rank

MK_QSL — Thu, 13 Aug 2015 13:50:21 GMT

CHECK !

Re: Average rank

Kushal_Chawda — Thu, 13 Aug 2015 14:21:56 GMT

Is your ranking is correct?

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

Re: Average rank

Anonymous — Thu, 13 Aug 2015 14:30:55 GMT

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.

Re: Average rank

Anonymous — Fri, 14 Aug 2015 06:27:41 GMT

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.