Oracle Analytics and AI

User_1H7AY

I want to show the budget for the previously selected year. I have a parameter "paramYear" that is
SELECT
"Financials - GL Balance Sheet"."Time"."Fiscal Year"
FROM
"Financials - GL Balance Sheet"
WHERE
"Financials - GL Balance Sheet"."Time"."Fiscal Year" = EXTRACT(YEAR FROM CURRENT_DATE)

I want to use this in another parameter named "paramPrevYear" and just subtract 1

I have tried
SELECT
"Financials - GL Balance Sheet"."Time"."Fiscal Year"
FROM
"Financials - GL Balance Sheet"
WHERE
"Financials - GL Balance Sheet"."Time"."Fiscal Year" = (@parameter ("paramYear")('2025')-1

I have also tried

SELECT @parameter ("paramYear")-1 from "Financials - GL Balance Sheet"."GL Balance (LC)"

Lastly I tried creating a calculation using several variations of the paramYear parameter. Everything I tried returns the previous year but when i use it in this filter (along with other variations) i get no results even though the year is working. I have also tried casting it as an integer even though it already is. No luck

FILTER(
FILTER(
"Financials - GL Balance Sheet"."GL Balance (LC)"."Balance Amount"
USING "Financials - GL Balance Sheet"."Time"."Fiscal Period" = @parameter ("paramFiscalPeriod")('Sep-25')
)
USING "Financials - GL Balance Sheet"."Time"."Fiscal Year" = @parameter ("paramPrevYear")(2025)
)

instead of

@parameter ("paramPrevYear")(2025) if I hardcode a year such as 2024 it works great. I even tried creating a calculation PrevYR which is

@parameter("paramYear")-1 . Again, it shows the correct year if I put it in a column but get no results using that calculation like

FILTER(FILTER("Financials - GL Balance Sheet"."GL Balance (LC)"."Balance Amount"USING "Financials - GL Balance Sheet"."Time"."Fiscal Period" = @parameter ("paramFiscalPeriod")('Sep-25'))USING "Financials - GL Balance Sheet"."Time"."Fiscal Year" = PrevYR even if i cast it as integer.

Any thoughts?

RVohra

CAST should have worked, was it like CAST(@parameter ("paramYear") AS INTEGER) - 1