Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 213 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Cumulative Measures by Fiscal Year and Month
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!!!!
Answers
-
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.
0 -
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!!!
0 -
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?
0 -
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!!
0 -
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.
0 -
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!!
0 -
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.
0 -
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.
0
