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

Incorrect Grand Total on derived fields

Received Response
3
Views
6
Comments
3580851
3580851 Rank 2 - Community Beginner
edited Aug 13, 2024 5:55PM in Oracle Analytics Cloud and Server

Hi All,

We are having issue with the Grand Total of a derived field. below are the details.

We are required to derive average daily number of orders based on the logic: Sum(Fact.number of order)/ (Time.number of days for the selected period)

Scenario 1

pastedImage_0.png

We have daily snapshots of data. When we display Grand Total, it displays on 83464 and does not divide by the total of no days.

Scenario 2: Monthly. The orders for each month are displayed right (total orders/no of days for that month) but the Grand total displays just Sum.

pastedImage_0.png

We are looking for a fix to be implemented at RPD level as this will be exposed for self-service and the users cannot change the Aggregation to server complex at report level.

Version: OBIEE 11.1.1.9

Thanks..

Answers

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    You don't say; -

    is your average computed; -

    a. in answers

    b. in rpd as a physical calc

    c. in rpd as a logical calc

    If b / c you also don't say what level of the time hierarchy (if any) the calculation is set to i.e. detail / month / not set?

    Also, if b /c what is your calculation set to in terms of aggregation and what exact formula do you have now in the rpd?

    Finally, if you have year in answers instead of year/month do you get the correct number?

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    X-D

  • 3580851
    3580851 Rank 2 - Community Beginner

    Hi Robert,

    Thanks for your response. Please find some details:

    The average is calculated in the logical column. We have 3 LTS, set at Date, Month and Year level.

    Avg=SUM("Number of Orders"*1.0)/"No of Days"

    No of Days is mapped to a opaque view (select count(distinct day_dt) AS NO_OF_DAYS,M_END_CAL_DT_WID,M_STRT_CAL_DT_WID from W_DAY_D

         group by M_END_CAL_DT_WID,M_STRT_CAL_DT_WID)

    We had to implement the above, as the requirement is "No of Days" should be all the days of the month irrespective of data availability. The same did not work with a outer join on Time and Fact Tables.

    The Grand Total doesn't display correctly just for Year as well.

    Please suggest if this could be done in a better way. Thank you.

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    okay; -

    Create a physical measure called Monthly Order Average. Use logic as below to assign values based on their number of days. Set its aggregation to SUM.

    pseudo logic

    number of orders / case when order month has 30 days then divisor = 1/30

    case when order month has 31 days then divisor = 1/31

    case when order month has 28 days and not leap year = 1/28

    else 1/29 end

    Make sure its detail is at day level in your time hierarchy.

  • [Deleted User]
    [Deleted User] Rank 2 - Community Beginner

    The precise approach and its pre-agg / post-agg implications and distinctions may get lost in translation there...

  • Robert Angel
    Robert Angel Rank 8 - Analytics Strategist

    Sure, I can only hope to point to the moon, he has to raise his sight to see it....