Ranking multiple dimensions and sorting, script

[#1]

Hello!

I am having a hard time visualizing how to do the following:

I have a list of deliveries with 10 different deviations that are integers. Each deviation is a dimension in the script representing the difference between when an order was supposed to achieve a milestone compared to when it actually did (so if it was 2 days late to deliver, the integer would be +2). What I want to do is have the script rank all of these milestones in descending order if the order was late and ascending order if the order was early and then use that rank to see which deviation was the highest, 2nd highest, etc if the order was late or lowest, 2nd lowest, etc if the order was early.

Any ideas? I am guessing it's some kind of grouping in the script with a peek (?).

Thanks for any help or ideas!

[puttemans]

Do you have a sample or an example of what you want to achieve?

With what you describe, you could not just load all, and then do the ranking/sorting in a chart?

[#3]

What I ultimately want to achieve is a chart that has a row for each delivery and then the top 3 dimensions that caused the delay or the delivery to be early like:

Delivery	1	2	3
123456	Dimension 7, 12	Dimension 5, 6	Dimension 1, 2
123457	Dimension 6, -11	Dimension 5, -6	Dimension 1, -2
123458	Dimension 4, 20	Dimension 10, 4	Dimension 3, 2

So, I was wondering if there's a way to create a dimension for each delivery that has rank 1, 2 and 3 that can easily be brought into a chart or graph without using a calculated dimension. I could probably do it with an extremely long nested if statement but I'm thinking there's probably an easier way.

Again, thank you for any ideas or suggestions you may have.

[#4]

Hi,

I think it depend on how you calculate your deviation.

You are supposed to have negative values if you perform "Supposed achievment"-"Real Achievement".

Supposed achievement     Real Achievement     Deviation

01/03/2016                       03/03/2016                     -2

04/03/2016                       03/03/2016                     1

In this case you can sort your chart as descending/ascending.

Then you can add a KPI containing the avg(Deviation) and color it in RED if it's a negative value and GREEN if it's positive to get a tendance.

Rgds,

Philippe

[#5]

Philippe,

Thanks for your reply. What I'm really looking for is a way to rank the multiple dimensions for each delivery so that I can choose the top N or bottom N values. Each delivery has potentially different values for each deviation depending on what happened in the supply chain. So, would it be a good idea to establish that in the script or within some sort of calculated dimension?

Martin

[#6]

Hi,

1-First you could create a table with 3 columns:

Delivery | Deviation value | Deviation ID

2-Then create 2 temporary tables :

First one sorted per Delivery & Deviation value (ascending).

Second one sorted per Delivery & Deviation  value (descending).

3-Your final table will be:

Delivery | if (Avg([Deviation values]>0, X,x) | if (Avg([Deviation values]>0, Y,y) | if (Avg([Deviation values]>0, Z,z)

where X Y Z are the 3 First values of descending sorted column and x y z are the 3 First values of ascending sorted column.

I know that my answer is not complete and I'm not sure it works. I didn't get time to test it also. But I hope it could help you.

Philippe

[#7]

Thanks, again for your help, Philippe. I see what you mean but I think that would only work for a small number of deliveries when I have to do it for thousands.

I am thinking that I have to create new dimensions in the script from 1 to 10 and rank each value for each delivery. Then, load them as 1, 2, 3, etc. My only concern is how to handle the values that tie, I think.

[#8]

Does the aggr function sort values? So is there a way to rank 10 dimensions of each delivery and then rank those values?