Can "Rank" function achieve this?

[NZFei]

Please see attached sample application.

Thanks.

[swuehl]

I am not exactely sure how you want to calculate your rank, thus I am attaching a sample with two different rank calculations,

one calculating total sales per department and group and ranking the department sales in the group dimension, one calculating total sales per department and group and ranking the sales for the selected group in the department dimension.

You should be able to adapt accordingly.

[NZFei]

Hi Swuehl,

Thank you very much for your reply. Table "CH01" is what I need to sort out.

In Group A there are only two stores (Store A and Store B). If we select Store A, all the three departments D1, D2 and D3 rank 2 in the group, which is correct by your calculation. However when we select Store B, all the three departments should rank 1 in the group but your calculation is still showing 2, which is not right.

Thanks anyway.

Fei

[swuehl]

Department3 shows rank 2 in both cases because there is a tie in ranking, Sum(sales) is 900 for both groups.

You can change the tie breaking a bit using the additional arguments to rank() function.

Why do you show 2 for Group A and 1 for Group B, if both groups share same sales amount?

[NZFei]

Sorry I didn't explain clearly what I want.

This is a table part of a benchmark group analysis application. In this application, stores compare their sales with benchmark group average and find out the rankings for department items within the group.

What I want to show is the department rank for a store within this store's group. The group here is the benchmark group for the store. So stores are selling all sorts of department items, what are the rankings for these departments within the group?

Users need to select one store to view the reports. CH01 is to show the item department rankings for the selected store within the group. Therefore all the rankings for the three departments should be 2 for Store A and 1 for Store B.

Thanks.

Fei

[swuehl]

Maybe like attached, bottom chart?

[NZFei]

Thanks Swuehl. It is working for my sample data. However when I apply it to my live application, it is not working.

I have modified my dataset in the new attached file. Now if we select Store A, D1 should rank 1 within Store A's group. While D2 and D3 should rank 2. If we select Store B, D1 should rank 2 and D2 and D3 should rank 1. However your calculation always show the same ranking.

That comes back to my root question. If we look at the F1 help for "Rank", it is to rank within the table for all the rows. So either the result will be 1 for one row, 2 for one row and 3 for one row for a three row table. Or, like what you have tried to help me, show the same number using "Total" in set analysis.

Fo my example, is it possible to show rank 1 for one row (D1) and 2 for other two rows (D2 and D3) when Store A is selected?

Thanks a lot!

Fei.

[swuehl]

Maybe. I think I am still confused how you want to calculate your rank.

Could you maybe explain this for the new sample QVW and explaining this with the data contained?

[NZFei]

Sure.

I have changed the sales for D1 for Store A from 100 to 10000.

So when Store A is selected, D1 should rank 1. D2 and D3 should still rank 2.

When Store B is selected, D1 should rank 2, D2 and D3 should rank 1.

Is it possible to achieve that?

Thanks

Fei

[swuehl · Accepted solution]

It's getting late here and I think I start to guess what you finally want to achieve.

Basically, you need to understand how the rank function works, then create a virtual table using advanced aggregation that creates the list of values you want to rank.

See my next try in the bottom chart.