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

Question

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 TotalJul-161311985571084112671037031837359Aug-16 111183495986130354134357054Sep-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-17Jul-167359318313119855710841126710370Aug-1670543435 1111834959861303541Sep-1661713531  1001473909711032

I am able to achieve above results up till now

Now below is the actual requirement:

MonthAug-16Sep-16Oct-16Nov-16Dec-16Jan-17Jul96%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:

Rohit Lalwani · Answer

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

Jerry Casey · Answer

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 · Answer

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.