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

Calculated Measure in AGO Function

Received Response
101
Views
6
Comments
Ebin Cherian
Ebin Cherian Rank 5 - Community Champion
edited August 13 in Oracle Analytics Cloud and Server

Hi All,

Oracle Business Intelligence 12.2.1.1.0

This is a question I received from 1 of the users. Users use AGO function at the report level on top of RPD measures and results are fine. Question is will AGO work for a calculated measure in answers. ?

For example , user created a new measure in analysis say COUNT(DISTINCT "Transaction Details"."Document Number"). Then created a new column in report and gave the below mentioned formula.

AGO(COUNT(DISTINCT "Transaction Details"."Document Number"), "Time"."Time"."Fiscal Year", 1)

pastedImage_0.png

Thanks

Ebin

Answers

  • Uttam.Singh
    Uttam.Singh Rank 4 - Community Specialist

    Hey Ebin,

    Yes Ago function works from Answers too. You need to ensure you have the Hierarchy Tree pulled in to answers to do this calculation and reference the "level" with right level of field.

    In the example below the level would be "Time"."Time"."Year".

    pastedImage_0.png

  • cesar.advincula.o
    cesar.advincula.o Rank 6 - Analytics Lead

    It depends on your business model, can you check the query behind

  • Ebin Cherian
    Ebin Cherian Rank 5 - Community Champion

    Hi,

    If I create a measure in the RPD for the formula COUNT(DISTINCT "Transaction Details"."Document Number") and use that column in the analysis, AGO works fine. But when I try to do count distinct in answers, it is not working and just displays the same value as shown in the above screenshot.

  • Uttam.Singh
    Uttam.Singh Rank 4 - Community Specialist

    Hey Ebin,

    This seems to be a defect in 12c. I faced the same issue and Oracle Provided a workaround to fix this.

    You can add one of the below in the Advance Tab --> Prefix

    SET VARIABLE OBIS_REDUNDANT_DISTINCT_OPTIMIZATION = 'DISABLED' ;

    OR

    SET VARIABLE OBIS_REDUNDANT_DISTINCT_OPTIMIZATION = 'LEAF_ONLY'

    pastedImage_0.png

  • Ebin Cherian
    Ebin Cherian Rank 5 - Community Champion

    Hi,

    My issue is not related to distinct. It is with using formula in AGO in report. I face the same issue even if I just use count instead of count distinct.

    Just want to confirm if there is any restriction on measure that it should be defined in the RPD ?

    Thanks

    Ebin

  • Uttam.Singh
    Uttam.Singh Rank 4 - Community Specialist

    Check this SR 3-16820476571 I created for the Ago issue.

    Bug 26642083 was reported in 12c version. I dont think you need to do anything specific in the RPD for Ago function to work.