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

calculating the Percentage using two calculated field columns

Received Response
31
Views
4
Comments
OMAR_BI
OMAR_BI Rank 2 - Community Beginner
edited Aug 13, 2024 6:01PM in Oracle Analytics Cloud and Server

Hello,

I am having problem with the Percentage calculation, I am calculating two values in two different columns, Now I want to take out the percentage using this two column values.

Max(IFNULL("Fact Table Name"."Col_1", 0) By "Fact Table Name"."Col 2", "Fact Table Name"."Col_3") = (Dividend)

MAX(IFNULL("Table Name"."Col_1"", 0) By "Fact Table Name"."Col_2", "Fact Table Name"."Col_3")+sum(IFNULL("Fact Table Name"."Col_4",0) By "Fact Table Name"."Col_2", "Fact Table Name"."Col_3") = (Divider)

Please Help Me

Thanks

Omar

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    OMAR_BI wrote:I am having problem ....

    Like what for example? What's the problem?

    But your model seems to have issues as you are aggregating attributes by fact columns ....

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

    +1 - something's rotten in the state of your RPD

  • OMAR_BI
    OMAR_BI Rank 2 - Community Beginner

    Hi Gianni,

    You might be right if we better do it in modelling (Repository). When I was calculating it individually its returning the correct value, but with the % its aggregating at all levels, if I split it with region field its displaying the right figure, as I remove its adding all totals and displaying incorrectly.

    So I found the solution by adding the region and run date granularity to the sums I am doing to both columns then it returned the correct value.

  • OMAR_BI
    OMAR_BI Rank 2 - Community Beginner

    When I was calculating it individually its returning the correct value, but with the % its aggregating at all levels, if I split it with region field its displaying the right figure, as I remove its adding all totals and displaying incorrectly.

    So I found the solution by adding the region and run date granularity to the sums I am doing to both columns then it returned the correct value.

    sum(Max(IFNULL("Table Name"."Col 1", 0) By "Table Name"."Col Region", "Table Name"."Col RunDate") by "Table Name"."Col RunDate")  /(sum(MAX(IFNULL("Table Name"."Col 1", 0) By "Table Name"."Col Region", "Table Name"."Col RunDate") by "Table Name"."Col RunDate")+sum(sum(IFNULL("Table Name"."Col 2",0) By "Table Name"."Col Region", "Table Name"."Col RunDate") by "Table Name"."Col RunDate"))*100