topic Re: Find Position in Sorted List in QlikView

Find Position in Sorted List

bdiamante — Fri, 04 Apr 2014 23:29:54 GMT

I am trying to find a way to calculate a specific position index of a dimension for a given expression.

For example: If we have the chart expression 'sum(sales)' and the chart dimension 'Region', how could I find the position of a specific region in the result? Let's say I am interested in the 'Northeast' region and I have the following results from the expression/dimension above:

Southeast: 120,000

Southwest: 105,000

Midwest: 78,000

Northeast: 72,000

West: 34,000

I would like to display in a text box the position of the 'Northeast' region. So my text box would contain the number 4, since 'Northeast' is 4th in sorted order.

Even though this seems trivial, I cannot find a built-in function that does this and I've been racking my brain but cannot come up with a solution. Any ideas?

Re: Find Position in Sorted List

nagaiank — Sat, 05 Apr 2014 00:12:08 GMT

Did you consider using Rank() function? If you add the column Rank(Sum(sales)), it will display 4 for your data.

Re: Find Position in Sorted List

mambi — Sat, 05 Apr 2014 00:33:08 GMT

for : Northeast you can use :

=only({<col1={'Northeast'}>} aggr((rank(sum((col2)))),col1))

Re: Find Position in Sorted List

VishalWaghole — Sat, 05 Apr 2014 05:39:14 GMT

Hi Brian,

Please find attached file, it will help you.

- Regards,

Vishal Waghole

Re: Find Position in Sorted List

MK_QSL — Sat, 05 Apr 2014 07:55:13 GMT

This can be achieved by below two expressions in TEXT BOX.

Please check enclosed file...

=Aggr(IF(Region = GetFieldSelections(Region),Rank(SUM({1}sales))),Region)

=Aggr(Rank(SUM({1}sales)),Region)

Re: Find Position in Sorted List

bdiamante — Sat, 05 Apr 2014 14:51:23 GMT

This is exactly what I was looking for thanks!