nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Oracle Communities

Oracle Analytics and AI

Child Item

Is there a way to get the difference between 2 rows as a Grand Total

SharonAtGorilla

I know that the msum function can get that, but I need to display it as a column total not another column.
For instance using the Financials -GL Balance Sheet Subject Area and using Fiscal year

Fiscal Year #Cash
2024 50,000
2025 48,000
Grand Total 2,000

Find more posts tagged with

Fusion AI Data Platform

Accepted answers

SharonAtGorilla

This worked for me. Row_count was a good clue. Thank you.

case COUNT(DISTINCT "Financials - GL Balance Sheet"."Time"."Fiscal Year")
when 1 then "Financials - GL Balance Sheet"."Balance Sheet Statement (LC)"."Cash"
when 2 then

sum(
case "Financials - GL Balance Sheet"."Time"."Fiscal Year"
when year(CURRENT_DATE) then "Financials - GL Balance Sheet"."Balance Sheet Statement (LC)"."Cash"
when year(CURRENT_DATE)-1 then "Financials - GL Balance Sheet"."Balance Sheet Statement (LC)"."Cash"*-1
end
)
end

All comments

RVohra

Can you see if you can use following formula in a new measure :

CASE
WHEN GROUPING("Fiscal Calendar"."Fiscal Year") = 1 THEN
MAX("Balances"."Ending Balance")
- MIN("Balances"."Ending Balance")
ELSE
"Balances"."Ending Balance"
END

SharonAtGorilla

case when Grouping("Financials - GL Balance Sheet"."Time"."Fiscal Year") = 1 then
max("Financials - GL Balance Sheet"."Balance Sheet Statement (LC)"."Cash")- min("Financials - GL Balance Sheet"."Balance Sheet Statement (LC)"."Cash")
else
"Financials - GL Balance Sheet"."Balance Sheet Statement (LC)"."Cash"
end

I get this error

Function Grouping is not defined by administrator.

RVohra

How about ROW_COUNT? Can you try this one …

case when row_count("Financials - GL Balance Sheet"."Time"."Fiscal Year") > 1

SharonAtGorilla

RVohra

@SharonAtGorilla , I am very glad to see your issue is resolved. As per community guidelines, appreciate if we can mark the Answer as Accepted Answer. It will help the community for future references. Thanks