Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

OBIEE 11g - set different level of calculation than it is on a report

Received Response
13
Views
4
Comments
3126915
3126915 Rank 1 - Community Starter
edited Aug 13, 2024 6:01PM in Oracle Analytics Cloud and Server

Hi All,

I am trying to make a calculation that would omit one level from the report.

I have to calculate difference between days based on the particular Stage - between Closed and In Progress, per product. The report would look like that and what I need is shown in the last column " Stage_Closed_Start - Stage_InProgress_Start (Days) "

Product ID     |      Stage       |     Stage_start_date    |  Stage_InProgress_Start  | Stage_Closed_Start  |   Stage_Closed_Start - Stage_InProgress_Start (Days)

----------------------------------------------------------------------------------------------------------------

1                   |  Submitted     |    01.01.2017              |    02.01.2017                    |    05.01.2017             |          3

1                   |  In progress   |    02.01.2017              |    02.01.2017                    |    05.01.2017             |          3

1                   |  Resolved      |    04.01.2017              |    02.01.2017                    |    05.01.2017             |          3

1                   |  Closed          |    05.01.2017              |    02.01.2017                    |    05.01.2017             |          3

So as you can see, I am trying to get the result by firstly calculating Stage_InProgress_Start  and Stage_Closed_Start  which need to omit the Stage and Stege_start_date levels and calculate it on Product level.

Product, Stage and Stage_start_date are 3 separate dimensions.

Of course calculation will work if I remove Stage and Stage_start_date from the report, but I need them there.

Any ideas how to calculate Stage_InProgress_Start , Stage_Closed_Start  and  Stage_Closed_Start - Stage_InProgress_Start (Days)?

My definition of physical column for calculating Stage_InProgress_Start is:

CASE WHEN  "MIB Data Warehouse"."Catalog"."OLAP"."Dim_WC_SSTAGE_Stage"."STG_NAME" = 'In progress' THEN "MIB Data Warehouse"."Catalog"."OLAP"."Dim_WC_TIME_D_StageStart"."CAL_DATE" END

I played here with levels, but if I set Stage and Stage start levels to Total, then it's doing an OUTER JOIN and I cannot filter for example by stage... :/

Thank you in advance!

Anna

Answers

  • Sherry George
    Sherry George Rank 7 - Analytics Coach

    Hi Anna,

    My suggestion would be to calculate this in your ETL or write a database function that you can call from the analysis.

  • SriniVEERAVALLI
    SriniVEERAVALLI Rank 6 - Analytics Lead

    You need to use the formula something like below in the BMM layer

    TIMESTAMPDIFF(SQL_TSI_DAY,"Stage_InProgress_Start" , "Stage_Closed_Start")

  • 3126915
    3126915 Rank 1 - Community Starter

    Hi Srini, thank you for your answer, but firstly I need to calculate Stage_InProgress_Start and Stage_Closed_Start - this is my main concern, how to calculate them on Product level?

  • 3126915
    3126915 Rank 1 - Community Starter

    Thanks George, I will do it in etl if there is no chaince, but I was hoping Oracle BI can handle such requests...