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

Can we perform custom calculations on top of the Pivot view in OBIEE?

Received Response
12
Views
6
Comments
Rohit Lalwani
Rohit Lalwani Rank 2 - Community Beginner
edited Aug 13, 2024 5:35PM in Oracle Analytics Cloud and Server

Requirement is to calculate Residual trend for customer’s i.e. % of customer remaining in each month:

  

Row LabelsJul-16Aug-16Sep-16Oct-16Nov-16Dec-16Jan-17(blank)Grand Total
Jul-161311985571084112671037031837359
Aug-16 111183495986130354134357054
Sep-16 100147390971103235316171

Row represents: Acquisition month of customer

Column Represents: Leaving month/churn month of customer

In the month of July total 7359 customers were acquired (present in Grand total column)  out of which 131 customers left in July itself and 198 left in Aug-16 and so on , so the customers remaining  or Active customers in Jul-16 are the ones under header as (blank) i.e. 3183

So after renaming column (blank) to Active and Grand Total to Acquired, below view is achieved:

  

MonthAcquiredActiveJul-16Aug-16Sep-16Oct-16Nov-16Dec-16Jan-17
Jul-167359318313119855710841126710370
Aug-1670543435 1111834959861303541
Sep-1661713531 1001473909711032

I am able to achieve above results up till now

Now below is the actual requirement:

  

MonthAug-16Sep-16Oct-16Nov-16Dec-16Jan-17
Jul96%88%73%58%48%43%
Aug 96%89%75%56%49%
Sep 96%90%74%57%

To calculate % remaining for Aug-16 which are acquired in Jul-16:So logic to calculate % remaining for each month is below:

total acquired in July-(customers left in July+left in Aug)/total acquired in July.....i.e. 7359-(131+198)/7359=96%

For Sep-16 in Jul-16 month:

7359-(131+198+557)/7359=88%

I am not sure how to calculate % remaining in pivot, If anyone have any idea about this then please share.

Thanks for reading my post:

Answers

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    I don't have an immediate answer, but isn't your illustration incorrect?  Should the losses in the Aug-16 and Sep-16 rows be shifted to the right?
    i.e., Customers gained in Sep-16 can't be lost in July or August of '16.

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    Can you pivot your data the other way?  If so, the calculated columns become simple:

    pastedImage_0.png

  • Rohit Lalwani
    Rohit Lalwani Rank 2 - Community Beginner

    Thanks Jerry for you response and you are correct data got shifted to the left side while posting, pardon me for that.

    I have updated the post with correct data.

  • Rohit Lalwani
    Rohit Lalwani Rank 2 - Community Beginner

    Using above approach, % remaining calculation can be achieved but it's very difficult for an business user to read the report, as it's an 6 monthly report.

  • Jerry Casey
    Jerry Casey Rank 6 - Analytics Lead

    My suggestion was really about the structure of your data model.
    Setting that aside, another approach would be to construct accumulating columns for each month of the report.  I'm assuming you have an "Acquired Date" and a "Loss Date" for each customer.

    You would create 6 monthly columns of this general formula:

    September example:

    Filter("Customer Count" using ("Loss Date" BETWEEN "Acquired Date" AND date '2016-09-30')).  This would give you six accumulated loss columns with which to do math against the "Acquired" column.

    You can them build a pivot table like the one you posted.
    That formula could be generalized using repository or presentation variables

  • Rohit Lalwani
    Rohit Lalwani Rank 2 - Community Beginner

    Thanks Jerry for immediate response, I understood both of your approaches, I will go with second one.