How to Calculate a Total Percentage on Only the Non Null or To Date Values in Oracle Analytics Cloud (Doc ID 2713871.1)

Last updated on NOVEMBER 03, 2023

Applies to:

Goal

An Analysis with a Pivot Table view reports the following, with a Grand Total on the Month:-
     Month, Week, Day, Current Year Sales, Previous Year Sales, % Variance to Previous Year

The measures are calculated via the following formulas:
    CY Sales:       FILTER("Retail Net Amount" USING ("Fiscal Year" = 2020))
    PY Sales:       FILTER("Retail Net Amount" USING ("Fiscal Year" = 2019))
    % VAR to PY:  FILTER("Retail Net Amount" USING ("Fiscal Year" = 2020)) / FILTER("Retail Net Amount" USING ("Fiscal Year" = 2019)) - 1

To date, all PY Sales values are known (that is, they are "not null"), but some CY Sales values are not yet known (that is, they are "null" for the "future" days in the Month/Week). As the daily ETLs are run, the report will return CY Sales values for another day etc.

Enabling the Grand Total on the Month does return the correct Total % VAR to PY value, however, the requirement is to have this Total % VAR to PY value be calculated for the PY Sales values where the CY Sales values are known ("not null") to date. 

See the example below which has been simplified for illustration to only one Week of the Month:-

Current Results:

See (832,405.86 / 1,624,423.09) - 1 = -48.76% which is correct based on the CY Sales and PY Sales values in the Total

Required Results:

See ( 832,405.86 / (230,086.78 + 236,615.78 + 221,401.35) ) - 1 = 20.97% which is calculated only on the PY Sales values for SUN-MON-TUE, that is, where the CY Sales values are "not null"

How can this be achieved?

Solution

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.