Oracle Analytics Forum

Home› Oracle Analytics and AI› Oracle Analytics and AI Forums› Oracle Analytics Forums› Oracle Analytics Forum

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Two or more columns applying Rank function in a single Analysis. (OBIEE)

Received Response
612
Views
4
Comments
User_PSZ9Q
User_PSZ9Q Rank 1 - Community Starter
edited Aug 13, 2024 7:21PM in Oracle Analytics Forum

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

  • Federico Venturin - Oracle
    Federico Venturin - Oracle Mod

    Hi @User_PSZ9Q ,

    I would proceed as follow:

    1. Create an analysis to get the top 30 product codes by Sales (Analysis 1)
    2. Create a second analysis to get the top 5 customers by Sales (Analysis 2)
    3. 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.
  • User_PSZ9Q
    User_PSZ9Q Rank 1 - Community Starter

    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.

  • Federico Venturin - Oracle
    Federico Venturin - Oracle Mod

    @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)

  • Federico Venturin - Oracle
    Federico Venturin - Oracle Mod

    Hi @User_PSZ9Q ,

    Did you test the proposed solution to keep everything in a single analysis? Does it work as expected?