Oracle Analytics Cloud and Server

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

OBIEE (12.2.1.4) I need an alternative to using a "union" in an analysis

Received Response
507
Views
17
Comments
2»

Answers

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    I'm a bit confused again.  How do the "All Weeks" rows differ from the Name Totals?  It seems redundant.  If it's necessary, you can repeat the 'All Weeks' query, and put a formula in the Name column like: "Table"."Name"||' Total'.  You'll have to do more conditional formatting, and possibly add a column to keep them sorting in the right order.

    Signing off for the day.  Cheers!

  • Tom D
    Tom D Rank 3 - Community Apprentice

    I realize that it seems redundant. Just know that I can also select individual weeks instead of "All Weeks".  Probably should have mentioned that earlier, but didn't want to complicate matters....

    I hope that makes it clear why I need the Name totals.

    I'll work on what you mentioned....

  • Tom D
    Tom D Rank 3 - Community Apprentice

    I got the Name Totals and Grand Totals lines working and in their correct places on the pivot table. 

    Only thing I can't get right is the pivot table Grand Totals % column on the right (right-most column). The Atts. and Cmps. are correct.

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    An option:

    In your pivot table try un-checking the totals you are using to create your Grand Totals columns, and in its place,

    Click the edit icon for  Qtrs, and then "Create a calculated item".  in the dialog box, put 'Qtr1'+'Qtr2'+'Qtr3+'Qtr4'.  You can name it Grand total.

    This may produce better results.

  • Tom D
    Tom D Rank 3 - Community Apprentice

    Not giving correct results.  Here are the 1st two rows of the Grand Total columns:

    GT.PNG

    I'm really struggling here. What did I do wrong?

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    Did you change the position of your Measure labels?

    pastedImage_0.png

    Also, My last advice may not work.  The calculated Item will just total the percentages, not recalculate them.

    In this simple example, the Column totals are correct:

    pastedImage_1.png

    But in your case, the complexity of dealing with unions is showing its ugly head.

    Another approach gets into the real strength of unions.

    You may have noticed the "Add a Result Column" option:

    pastedImage_2.pngIf you click on one of the "legs", then click on "Result Column", you'll see it.

    This allows you to do calculations at the header level.

    All formulas use column references saw_0, saw_1,saw_2, etc, starting on the left.

    You can construct an alternate Percentage column there and see how that works.

    Not knowing your query, lets say your column order is BIN, Week, Qtr, Name, Attempts, Completions

    Percent would be 100*SUM(saw_5 by saw_0,saw_1,saw_2,saw_3)/SUM(saw_4 by saw_0,saw_1,saw_2,saw_3)

    The BIN and name changes that you've created on the "Totals" queries will still work with this method.

    Replace your old % column with this new one.

    I know this is getting into the weeds, but your requirements are extremely complex/convoluted.

    I'd have talked your customer into a simpler presentation, if I could.

  • Tom D
    Tom D Rank 3 - Community Apprentice

    Jerry,

    I got something to work with that last approach!

    I appreciate your diligence here.  Much appreciated!

    Tom