topic Re: Ranking multiple dimensions and sorting, script in QlikView

Ranking multiple dimensions and sorting, script

Tue, 01 Mar 2016 12:57:56 GMT

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!

Re: Ranking multiple dimensions and sorting, script

puttemans — Tue, 01 Mar 2016 14:04:56 GMT

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?

Re: Ranking multiple dimensions and sorting, script

Tue, 01 Mar 2016 14:20:20 GMT

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.

Re: Ranking multiple dimensions and sorting, script

Tue, 01 Mar 2016 15:51:20 GMT

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

Re: Ranking multiple dimensions and sorting, script

Tue, 01 Mar 2016 18:25:27 GMT

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

Re: Ranking multiple dimensions and sorting, script

Thu, 03 Mar 2016 16:10:06 GMT

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

Re: Ranking multiple dimensions and sorting, script

Mon, 07 Mar 2016 13:00:57 GMT

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.

Re: Ranking multiple dimensions and sorting, script

Fri, 11 Mar 2016 13:56:04 GMT

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