Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
MK
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
For rank, try this:
Aggr(Rank(Sum({1}Sales)), Year, Customer)
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.
I did not add the expression to that one. Now I did and have updated the image as well
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.
after little modification for allowing other filters, this working perfectly.
Thanks..!
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.
May be this?
Only({<Customer = {'A'}>}Aggr(Rank(Sum({1}Sales)), Year, Customer))
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))
Yay!!!!