Oracle Fusion AI Data Platform

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Fusion AI Data Platform› Oracle Fusion AI Data Platform

Categories

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

Getting the previous year from a parameter

Received Response
45
Views
4
Comments
User_1H7AY
User_1H7AY Rank 3 - Community Apprentice
in Oracle Fusion AI Data Platform

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?

Answers

  • RVohra
    RVohra Rank 7 - Analytics & AI Coach

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

  • User_1H7AY
    User_1H7AY Rank 3 - Community Apprentice
    https://community.oracle.com/products/oracleanalytics/discussion/comment/72335#Comment_72335

    Yes. I also tried casting the calculation and anything else I could think of.

  • RVohra
    RVohra Rank 7 - Analytics & AI Coach

    Log an SR, please see if Oracle Support can help. They have a bigger knowledge base and also access to the Product Development Team who can assist to determine if it is supported or not. Thanks

  • Caroline Blanchard
    Caroline Blanchard Rank 6 - Analytics & AI Lead

    Hi @User_1H7AY Have you tried the trailing year functionality built in to the GL Balance Sheet subject area? Something like this for previous year:
    SELECT "Financials - GL Balance Sheet"."Time"."Fiscal Year"
    FROM "Financials - GL Balance Sheet"
    WHERE
    ("Financials - GL Balance Sheet"."Time"."Trailing Year Number" = 1)
    ORDER BY 1 ASC NULLS LAST

    image.png

    And then repeat the process with Trailing Year Number =0 to obtain current year.