topic Same Rank after Selection in QlikView

Same Rank after Selection

MK_QSL — Fri, 05 Aug 2016 11:04:50 GMT

I need urgent help on rank analysis.

Check enclosed file for data and requirements.

What I need is already explained in file.

I need urgent help on rank analysis.

Check file for data and requirements.

What I need is already explained in file.

Thanks

Data is something like below

Data:
Load * Inline
[
Year,   Customer, Sales
2013,   A, 120
2013,   B, 200
2013,   C, 250
2013,   D, 245
2013,   E, 175
2013,   F, 100
2014,   A, 175
2014,   B, 225
2014,   C, 240
2014,   D, 300
2014,   E, 200
2014,   F, 135
2015,   A, 200
2015,   B, 160
2015,   C, 180
2015,   D, 240
2015,   E, 200
2015,   F, 160
2016,   A, 300
2016,   B, 225
2016,   C, 145
2016,   D, 200
2016,   E, 180
2016,   F, 175
];

I have created two tables

When I select any customer, I need rank as below.

Say I select Customer A

Rank by Year table should show me
2013 Rank 5
2014 Rank 5
2015 Rank 2-3
2016 Rank 1

Sales Analysis table should show as

2013 2014 2015 2016
A 5 5 2-3 1

Re: Same Rank after Selection

sunny_talwar — Fri, 05 Aug 2016 11:12:41 GMT

For rank, try this:

Aggr(Rank(Sum({1}Sales)), Year, Customer)

Re: Same Rank after Selection

MK_QSL — Fri, 05 Aug 2016 11:13:45 GMT

Thanks Sunny,

But Sales Analysis table is not showing proper rank.

NOTE: Sorry for my earlier reply. Looks like it's working. Let me check and will come back to you.

Re: Same Rank after Selection

sunny_talwar — Fri, 05 Aug 2016 11:14:56 GMT

I did not add the expression to that one. Now I did and have updated the image as well

Re: Same Rank after Selection

sunny_talwar — Fri, 05 Aug 2016 11:15:56 GMT

You can also try this:

Alt(Aggr(Rank(Sum({1}Sales)), Year, Customer), Rank(Sum(Sales)))

Will show regular rank when Customer isn't selected and will show Customer specific rank when a single customer is selected.

Re: Same Rank after Selection

MK_QSL — Fri, 05 Aug 2016 11:33:07 GMT

after little modification for allowing other filters, this working perfectly.

Thanks..!

Re: Same Rank after Selection

MK_QSL — Fri, 05 Aug 2016 14:04:20 GMT

stalwar1

What if I want below Rank by Year table for Customer A without selecting customer A.

i.e. Rank by Year table should show us Rank of Customer A for each Year compare to all other customers for the same year.

Re: Same Rank after Selection

sunny_talwar — Fri, 05 Aug 2016 14:06:51 GMT

May be this?

Only({<Customer = {'A'}>}Aggr(Rank(Sum({1}Sales)), Year, Customer))

Re: Same Rank after Selection

MK_QSL — Fri, 05 Aug 2016 14:08:21 GMT

I also tried below and about to write...thnx...

My mind start functioning again !! LOL

Only({<Customer={'A'}>} Aggr(Rank(Sum({<Customer>}Sales),1,1), Year, Customer))

Re: Same Rank after Selection

sunny_talwar — Fri, 05 Aug 2016 14:12:25 GMT

Yay!!!!