Categories
Two or more columns applying Rank function in a single Analysis. (OBIEE)
Would like to ask if it is possible to apply rank function and then filter to two fields without messing up each of those ranks?
To be more specific, one of rank is to order the sum of sales by product code whilst the other is to order sum of sales by customer. And the analysis requirement is to filter top 30 product code and top 5 customer contributing to most sales (i.e. sum(sales).
Have tried Union feature from analysis criteria, but seems doesn't work.
Please advise any potential features/functions/strategies which may apply to this analysis. MILLION THANKS!!!
Answers
-
Hi @User_PSZ9Q ,
I would proceed as follow:
- Create an analysis to get the top 30 product codes by Sales (Analysis 1)
- Create a second analysis to get the top 5 customers by Sales (Analysis 2)
- Create a third analysis and add a filter for Product Code based on Analysis 1, and a filter on Customer based on Analysis 2. This third analysis will give you the desired results.
0 -
Thanks for your swift reply @Federico Venturin
though your proposed methods would work, the purpose of my post is to try to keep the dataset to a single analysis rather than creating multiple analysis to foster easy maintenance and update. Appreciate if you can suggest whether it would work.
0 -
@User_PSZ9Q you can do it with a single analysis by creating SQL filters to filter product codes and customers.
For instance:
"Product"."Product Code" IN (SELECT "Product"."Product Code" FROM "Subject Area" WHERE RANK("Measures"."Sales") <= 30)and
"Customer"."Customer" IN (SELECT "Customer"."Customer" FROM "Subject Area" WHERE RANK("Measures"."Sales") <= 5)0 -
Hi @User_PSZ9Q ,
Did you test the proposed solution to keep everything in a single analysis? Does it work as expected?
0
