Categories
- All Categories
- 75 Oracle Analytics News
- 7 Oracle Analytics Videos
- 14K Oracle Analytics Forums
- 5.2K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 40 Oracle Analytics Trainings
- 59 Oracle Analytics Data Visualizations
- 2 Oracle Analytics Data Visualizations Challenge
- 3 Oracle Analytics Career
- 4 Oracle Analytics Industry
- Find Partners
- For Partners
Displaying last 12 Months of Data based on Current Month Parameter in OAC
Hello All,
We have a requirement to show last 12 months of data based on Current Month Period in OAC, we know this can be achieved through OAC Classic Home in FDI,Would like to know how this can be achieved in FDI OAC.
In below example we have used filters in different columns to show data by period which is static. wanted to achieve this in dynamic method.
Please let us know your thoughts on displaying last 12 months of data based on current month filter to accommodate dynamic solution
Thanks,
Vaigu
Best Answer
-
- 1. Create a custom table with fiscal period column and corresponding custom date column containing the first date of the fiscal period.
2. Extend the prebuilt time dimension using this custom table.
3. Create a filter based on this custom date column and bind a parameter.
4. Create an expression filter using
"Financials - GL Balance Sheet"."Time"."Custom Date" between TIMESTAMPADD(SQL_TSI_MONTH, -12,@parameter("Fiscal Date")(current_date)) and @parameter("Fiscal Date")(current_date)
Other suggestions are welcome.
Regards,
Bala.
0 - 1. Create a custom table with fiscal period column and corresponding custom date column containing the first date of the fiscal period.
Answers
-
Hi @Vaigu_27 try if you can use this session variable as filter -
for example:
("HCM - Workforce Core"."Time"."Month" = VALUEOF(NQ_SESSION.TRAILING_12_GREGORIAN_PERIODS))
Raghu
1 -
I tried to create this filter in DV Workbook couldn't proceed much, is there any documents/blog available to implement this
Additionally do you think with this approach we can display 12 months of actuals based on the current period selection
Vaigu
0 -
Please try using 'Dashboard Filters' visual in DV and bind a parameter with the dashboard filter and use the parameter in the expression filter as follows:
"PC4"."Posted Date"."Date"<=TIMESTAMPADD(SQL_TSI_MONTH, -12, @parameter("Latest Date")(CURRENT_DATE))
0 -
an another version of this can be like below
"HCM - Workforce Core"."Time"."Date" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH,-11,CURRENT_DATE) AND CURRENT_DATE
0 -
@Raghavendra Raghunath-Oracle @BalagurunathanBagavathy-Oracle ,
As suggested used a 'Dashboard Filter ' Visual in DV, created a Bind Parameter for Fiscal Period, modified parameter with below expression, getting below error in the screenshot
"Financials - GL Balance Sheet"."Time"."Fiscal Period" <= TIMESTAMPADD(SQL_TSI_MONTH,-11,CURRENT_DATE) AND CURRENT_DATE
Do review ,let me know your inputs on what am i doing different here.
Vaigu
0 -
Instead of using dashboard filters and parameter, if you simply the expression filter below, does it help?
"Financials - GL Balance Sheet"."Time"."Fiscal Date" between TIMESTAMPADD(SQL_TSI_MONTH,-11,CURRENT_DATE) AND CURRENT_DATE
0 -
@BalagurunathanBagavathy-Oracle
Our requirement to display last 12 months of Actual values based on monthly prompt selection from users, hence tried with Filter & Parameter. please advise on any alternate approach to attain this
, Financials - Balance SHeet doesn't have Fiscal Date has only Fiscal period, wrote the expression based on the code getting below is the error
"Financials - GL Balance Sheet"."Time"."Fiscal Period" BETWEEN TIMESTAMPADD(SQL_TSI_MONTH, -11,CURRENT_DATE) AND CURRENT_DATE
Invalid Expression. A general error has occurred. Message returned from OBIS [ecid:6f99537b-1f72-417e-9ea9-cfa0d78adc82-000640b6,0:1:1:6 ts:2024-07-03T06:32:39.625+00:00]. Query Failed: A comparison is being carried out between non-compatible types VARCHAR and DATE. (HY000)
0 -
@Raghavendra Raghunath-Oracle @BalagurunathanBagavathy-Oracle ,
In the Out of Box Financials - GL Balance Sheet Subject Area has Time.Fiscal Period values in Jan-23..Dec-23 format
I have created a Parameter to select the Month Value, since Month is at Varchar Jan-23,Feb-23
Created Expression Filter using below syntax to show trailing 12 Months Period
"Financials - GL Balance Sheet"."Time"."Fiscal Period" >=(TIMESTAMPADD(SQL_TSI_MONTH, -12,@parameter("Fiscal_Period")('Jul-23'))) causes below error
Invalid Expression. Validation of parameter values failed. A general error has occurred. Message returned from OBIS [ecid:6f99537b-1f72-417e-9ea9-cfa0d78adc82-00067d34,0:1:1:6 ts:2024-07-03T13:42:09.535+00:00]. Query Failed: Function TimestampAdd is called with an incompatible type. (HY000)
Created Filter Expression to trailing 12 Months.
"Financials - GL Balance Sheet"."Time"."Fiscal Period" >=(TIMESTAMPADD(SQL_TSI_MONTH, -12,@parameter("Fiscal_Period")('Jul-23')))
can you guide me with correct expression to display last 12 months of data based on Month Parameter selection in DV ReportThanks,
Vaigu
0 -
- 1. Build a custom table with fiscal period column and corresponding custom date column that contains 1st day of the period.
- 2. Extend the prebuilt time dimension in "Financials - GL Balance Sheet" subject area using this custom table.
- 3. Use this custom date column in the filter
- 4. Bind it with a parameter
- 5. Create an expression filter using the custom date column.
Other suggestions are welcome.
Regards,
Bala.0
