topic Re: Re: Re: Aggregation over sub dimensions in QlikView

Aggregation over sub dimensions

Fri, 10 Oct 2014 08:16:44 GMT

Greetings.

I have a feeling the answer involves the Aggr() function; however, as yet I can not get it to work.

I have a table similar to the example below

Order #	SKU	Lead time (days)	Rank
169339	1112	70	3
169339	2231	111	6
169339	3451	141	8
169339	3566	166	9
169339	8582	89	5
178040	1112	58	1
178040	2231	71	4
171193	1112	60	2
178111	1112	121	7

I can perform ranking and averaging etc on the table when I have one SKU selected, but when the selection is removed the ranking and average refers to all data displayed in that column (as shown above).

What I want to achieve is have an unfiltered table but show the ranking etc per SKU.

For example:

Order #	SKU	Lead time (days)	Rank
169339	1112	70	3
169339	2231	111	2
169339	3451	141	1
169339	3566	166	1
169339	8582	89	1
178040	1112	58	1
178040	2231	71	1
171193	1112	60	2
178111	1112	121	4

Please can anyone help?

Re: Aggregation over sub dimensions

anbu1984 — Fri, 10 Oct 2014 08:27:27 GMT

Aggr(Rank([Lead time]),SKU)

Re: Aggregation over sub dimensions

Fri, 10 Oct 2014 09:07:43 GMT

Hii,

Please use this expression for solution:-

=aggr(rank([Lead time (days)]),SKU,[Order #])

And also find the attached QVW .

Re: Re: Aggregation over sub dimensions

Fri, 10 Oct 2014 09:58:51 GMT

Firstly - Thanks to everybody for their advice

I have attached an example and for unknown reasons your suggestion (which works in your example) acts differently in mine???

They are basically the same but I am unable to spot what's wrong...

Re: Re: Aggregation over sub dimensions

Fri, 10 Oct 2014 10:01:16 GMT

Thank you for your advice. I have now attached an example in reply to Prince Anand. This didn't quite solve my problem but I think it is a step in the right direction.

Re: Re: Aggregation over sub dimensions

Fri, 10 Oct 2014 10:27:21 GMT

The Only difference is that It is giving result in Descending Order but

I think you want the result in Ascending Order.

Thanks & Regards

Prince Anand

Re: Re: Re: Aggregation over sub dimensions

Fri, 10 Oct 2014 10:34:28 GMT

Please check the expression for taht solution:-

=aggr(rank(-[Lead time (days)]),SKU,[Order #])

I think you want the result like this:-

Re: Re: Aggregation over sub dimensions

Fri, 10 Oct 2014 10:36:06 GMT

The difference I am talking about is that the table on the left has more results visible. For example, using this expression I am losing all results for SKU 3487 and only getting partial results for 1123...

I have been trying to play with the aggr() function but not arrived at the desired result.

Thanks again

Oli

Re: Re: Re: Re: Aggregation over sub dimensions

Fri, 10 Oct 2014 10:40:22 GMT

I tried this

=aggr(rank(total [Lead time (days)],0,1),[Lead time (days)], SKU,[Order #])

which shows all the results but I am back to the rank being for the whole table not per SKU/Lead time.

Also - when I removed the total from the rank formula I get all lines as rank 1.

Re: Re: Re: Aggregation over sub dimensions

anbu1984 — Fri, 10 Oct 2014 11:05:10 GMT

Check this qvw

=Alt(aggr(rank(-[Lead time (days)]),SKU,[Lead time (days)]),Above(aggr(rank(-[Lead time (days)]),SKU,[Lead time (days)])))

Re: Re: Re: Re: Aggregation over sub dimensions

Fri, 10 Oct 2014 11:22:21 GMT

Hi Anbu

Thank you for your help. I am looking in the QlikView help file but can't yet figure out how your solution works. As I am trying to learn from this could you briefly explain how this expression works?

Am I right in thinking that this:

aggr(rank(-[Lead time (days)]),SKU,[Lead time (days)])

returns the ranking, while the rest of the alt() and above() have been used to fix the sort order??

Thanks and kind regards

Oli

Re: Re: Re: Aggregation over sub dimensions

anbu1984 — Fri, 10 Oct 2014 11:46:46 GMT

aggr(rank(-[Lead time (days)]),SKU,[Lead time (days)])

- Aggr on SKU,[Lead time (days)] creates a table like below in memory. Aggr is used to ignore your other dimension Order # while ranking

SKU	Lead time (days)
1112	70
2231	111
3451	141
3566	166
8582	89
1112	58
2231	71
1112	60
1112	121

- Then Rank is calcuated on [Lead time (days)] based on above data.

Lead time, Rank

58, 1

60, 2

70, 3

121, 4

Rank of negative Lead time

Lead time, Rank

-58, 4

-60, 3

-70, 2

-121, 1

Alt( expr, Above(expr)) - Alt returns value of expr which provides non-null value. In our case, Rank generates null value, if you have same Lead times. So Alt is used to return to same rank from the above row

Re: Aggregation over sub dimensions

anbu1984 — Fri, 10 Oct 2014 12:00:27 GMT

Generating rank in script

Initial:

LOAD [Order #],

SKU,

[Lead time (days)],

[Desired result]

FROM

(biff, embedded labels, table is Sheet1$);

NoConcatenate

Final:

Load *,If(SKU = Previous(SKU) And [Lead time (days)] = Previous([Lead time (days)]),Peek(Rnk1),Rnk) As Rnk1;

Load *,AutoNumber(RecNo(),SKU) As Rnk Resident Initial Order by SKU,[Lead time (days)];

Drop table Initial;