My Oracle Support Banner

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 SEPTEMBER 24, 2020

Applies to:

Oracle Analytics Cloud - Version N/A and later
Information in this document applies to any platform.

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

Month Week Day CY Sales PY Sales %VAR to PY
9    36 SUN 309,593.18 230,086.78 34.55%
    MON 243,346.32 236,615.78 2.84%
    TUE 279,466.36 221,401.35 26.23%
    WED   229,331.11  
    THU   255,036.74  
    FRI   232,271.06  
    SAT   219,680.27  
TOTAL     832,405.86 1,624,423.09 -48.76%

 

 

 

 

 

 

 

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"

Month Week Day CY Sales PY Sales %VAR to PY
9    36 SUN 309,593.18 230,086.78 34.55%
    MON 243,346.32 236,615.78 2.84%
    TUE 279,466.36 221,401.35 26.23%
    WED   229,331.11  
    THU   255,036.74  
    FRI   232,271.06  
    SAT   219,680.27  
TOTAL     832,405.86 1,624,423.09 20.57%

How can this be achieved?

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution


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