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

Pivot table Sub Total for records within each Accounting Period.

Received Response
11
Views
8
Comments
User_UT27A
User_UT27A Rank 2 - Community Beginner
edited Aug 13, 2024 5:24PM in Oracle Analytics Cloud and Server

I want to achieve Total column only for records with price mentioned above for each AP excluding columns with "WAC".

Ex: For AP 01, I Want to have Total column Before "WAC", which totals values with prices 5.111 and 5.112 excluding values in "WAC".

Please help me how to get this done in OBIEE 11g.

Thanks

Prabhu

pastedImage_1.png

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    You can't use the automated totals/subtotals options of the pivot, because they would be on everything or nothing.

    Depending on what your "5.111", "5.112" and "WAC" are (measures, values of a dimension attribute etc.) you will need to add an element there with your calculation.

    If it's measures add a new one with the required formula, if it's values of a dimension attribute you could try adding a new group entry or a calculated item.

  • Joel
    Joel Rank 8 - Analytics Strategist

    Adding to @Gianni Ceresa response, you will need to create a column with a formula with logic similar to this:

    FILTER(SUM(measure BY AP dimension column) USING (2nd dimension column <> 'WAC'))

  • User_UT27A
    User_UT27A Rank 2 - Community Beginner

    Hi

    Thanks, I will try this, Yes values "5.111", "5.112" and "WAC" are measures. Yes automated subtotals are for everything or nothing.

    Thanks

    Prabhu

  • User_UT27A
    User_UT27A Rank 2 - Community Beginner

    Thanks Joel, I will try this option and let you know what happened.

  • User_UT27A
    User_UT27A Rank 2 - Community Beginner

    I created a column with formula which is another measure, But how does this work as pivot subtotal?

  • User_UT27A
    User_UT27A Rank 2 - Community Beginner

    pastedImage_0.png

    How this works as pivot column?

    Thanks

    Prabhu

  • Gianni Ceresa
    Gianni Ceresa Mod

    How can the new measure you created be a row?

    How can that be a measure if few post ago you said "5.111", "5.112", "WAC" are the measures? That's impossible!

    The measure it's either your rows or your columns, it can't be both at the same time, you better figure out exactly what is what.

  • User_UT27A
    User_UT27A Rank 2 - Community Beginner

    Sorry my mistake, Price, quantity and value are all part of same Aggregate Fact table with item and Calendar as dimension tables. But here we are using quantity and value as measures while price is used as a pivot column,that's the reason i am having this issue.

    Thanks

    Prabhu