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

Cumulative Measures by Fiscal Year and Month

User_TYZ1T

Hi All,

I am trying to create a Graph report to show cumulative values by Fiscal Year and Month. Our fiscal year starts from September last year and goes till August next year. When I am trying to do RSUM on the measure its displaying cumulative values by calendar year but not fiscal year. Can anyone please help me how can I achieve this. My columns in report are as below:

Calendar Month concatenated with Year

Number of Samples

Formula used: RSUM(Number of Samples by Fiscal Year,Month)

Thanks in Advance for the help!!!!

Find more posts tagged with

Accepted answers

All comments

Gianni Ceresa

Can't you use the time series calculations?

TODATE should be able to sum all the required periods correctly. If you did model your fiscal calendar as alternative hierarchy in your time dimension, it will works just fine.

User_TYZ1T

Thanks for your response.

As per your suggestion I have setup the fiscal calendar as alternative hierarchy for Date Dimension and created a new logical column with the below formula but its giving me the below error:

TODATE(CAST("Core"."Fact"."Number of Samples" as DOUBLE), CAST("Core"."Date"."Fiscal Year" as smallINT))

Error - [nQSError: 27002] Near <CAST>: Syntax error [nQSError: 26012] .

Number of Samples Logical column - count(distinct Sample_Fact.UNIQUE_COLUMN) - VARCHAR Datatype

Fiscal Year- VARCHAR Datatype

Can you please check and let me know if I am using the correct syntax to sum all the required periods correctly:

Thanks in advance for your help!!!

Gianni Ceresa

You don't have to cast the fiscal year at all, and the second parameter for TODATE isn't a column but the hierarchy level of your time hierarchy (and that's why you can tell OAS/OBIEE to work on the fiscal calendar or normal calendar when they are modeled as alternative hierarchy, because the level will tell the system which calendar to use).

Why are you casting "Number of Samples"? How come that thing isn't a number already?

User_TYZ1T

Thank you for your response.

Number of Samples has Varchar as datatype as UNIQUE_COLUMN has Varchar as datatype in table. Do I need to remove cast on

Number of Samples to make it work.

Thanks in advance for your help!!

Gianni Ceresa

If your measure is a varchar, it depends what the aggregation is. If you use COUNT on it, no need to cast anything, if you use SUM, you have to cast the definition of the column itself in the RPD because the SUM has otherwise no way to work.

All in all, you aren't supposed to cast the measure because TODATE only change the scope of calculation over multiple periods, but doesn't decide at all what aggregation it does: the RPD definition of the measure decide what the aggregation function is.

User_TYZ1T

Thank you for your response.

If I dont use CAST on measure then I am getting invalid expression error. I am using the below formuula:

TODATE("Core"."Fact"."Number of Samples", "Core"."Date"."Fiscal Year")

Thanks in advance for your help!!

Gianni Ceresa

The second parameter of the function isn't a column name but the name of the hierarchical level: is your hierarchical level named "Core"."Date"."Fiscal Year" ?

This one looks more like your fiscal year column, not the hierarchy level.

User_TYZ1T

Thanks for your response.

I was able to fix the issue by selecting the hierarchy level instead of the column as per your suggestion.

Thank you once again for your help.