Pivot view calculation going wrong — Oracle Analytics

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 view calculation going wrong

Received Response
72
Views
11
Comments
User_CEU9I
User_CEU9I Rank 5 - Community Champion
edited Aug 13, 2024 6:06PM in Oracle Analytics Cloud and Server

Hi all,

I have requirement where dashboard prompt have year(Pyear_pv) and  month (Pmonth_pv) as filters.

Report is as follows:

pastedImage_0.png

2016, 2015 columns are populating through presentation variables (heading also through PV ).

The script for 2016 column is:

case when "- Shipment Period Dimensions"."Year"=('@{Pyear_pv}{2016}') then count(distinct "- Shipment Dimensions"."Power Unit GID") end

Here "power unit GID" is dimension column and can have duplicate values.

The script for 2015 column is:

case when "- Shipment Period Dimensions"."Year"= ('@{Pyear_pv-1}{2015}') then count(distinct "- Shipment Dimensions"."Power Unit GID") end

Here i need to get data for current year (selected through db prompt) and previous year.

NOTE: I am using obiee 11g, cloud instance (oracle FTI instance), there is no time series feature.

Problem:

In table view i am getting correct value. But when i convert into pivot view then the values are not coming correct.

The report format is pivot format. Please let me know how to achieve the correct result.

Regards,

Ram.

«1

Answers

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

    You have "Year measures" and a split by Year in terms of dimensional hierarchy/attribute? Why?

  • User_CEU9I
    User_CEU9I Rank 5 - Community Champion

    Hi,

    Because of incorporating time series functionality (Current year and previous year).

    At my report level i put month as "is prompted".

    User can select year and month so that my report will filter based on user selection. If i select year 2016 and December the result as shown below.

    pastedImage_0.png

    Regards,

    Ram.

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

    Just function-wise the proper function to use is "FILTER" rather than CASE.

    Example:

    pastedImage_2.png

    pastedImage_1.png

    Result:

    pastedImage_3.png

    So as long as the split by dimensional attribute exists (case table) this works but as soon as you drop that it breaks. That's expected.

    But what I still don't get conceptually: If you define precisely TWO years in your prompt...why not just use that standard functionality of having the data filtered and the dimensional attribute doing the split??

    pastedImage_4.png

  • User_CEU9I
    User_CEU9I Rank 5 - Community Champion

    Hi,

    Please find the difference for the following two scenarios:

    1. I have used measure column : shipment count.

    pastedImage_0.png

    For 2016: FILTER("- Shipment Facts"."Shipment Count" using("- Shipment Period Dimensions"."Year" in ('2016')))

    2016 query gives only 2016 data.

    For 2015: FILTER("- Shipment Facts"."Shipment Count" using("- Shipment Period Dimensions"."Year" in ('2015')))

    2015 query gives only 2015 data (please see the table view).

    The result is proper in pivot view.

    2. I have used dimension column: Power unit GID.

    pastedImage_1.png

    For 2016: FILTER(count(distinct "- Shipment Dimensions"."Power Unit GID") using("- Shipment Period Dimensions"."Year" in ('2016')))

    2016 query is giving both data.

    For 2015: FILTER(count(distinct "- Shipment Dimensions"."Power Unit GID") using("- Shipment Period Dimensions"."Year" in ('2015')))

    2015 query is giving both data (2016 also).

    Pivot view data is not correct.

    I am not understand what is going wrong and how to correct this.

    regards,

    Ram.

  • User_CEU9I
    User_CEU9I Rank 5 - Community Champion

    one more thing ...my dashboard prompt as follows

    pastedImage_0.png

    if user select 2017 year then in my report, i have to show both selected and previous year(2016) data.

    regards,

    ram.

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    build your counts as measures in the fact ... that's what they are.

  • User_CEU9I
    User_CEU9I Rank 5 - Community Champion

    Hi all,

    Please let me know any possible solution or any workaround solution is most appreciated.

    regards,

    ram

  • Mark.Thompson
    Mark.Thompson Rank 6 - Analytics Lead

    What happens if you display the Year column going down instead of the measure labels? 

  • Thomas Dodds
    Thomas Dodds Rank 8 - Analytics Strategist

    I did give you a solution (not a workaround) ... you know the pivot works when using a fact (it's designed to do just that) ... what you are doing (counting dim attributes) is actually logically creating facts ... so put those in the RPD in your logical fact table (or create a logical fact table at the same LTS grain as your fact).

  • User_CEU9I
    User_CEU9I Rank 5 - Community Champion

    Hi,

    It's cloud instance (Oracle FTI instance) and do not have access to RPD. I have to give solution in the report only. Please let me know any other solution.

    regards,

    ram.