Oracle Analytics Cloud and Server

Home› Oracle Analytics› Oracle Analytics Forums› Oracle Analytics Cloud and Server› Oracle Analytics Cloud and Server

Categories

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

Unable to use filter by parameter selected year - 1

Received Response
31
Views
4
Comments
Kenneth_W
Kenneth_W Rank 1 - Community Starter
in Oracle Analytics Cloud and Server

I have created parameter using date - year.

image.png

Trying to create below calculation to put it as parameter for one of the table for previous year. E.g. when choosing 2024, the table will then be filter by 2023 and so on. However, error is prompt. Could any expert help? Thank you.

image.png

Tagged:

Answers

  • Gianni Ceresa
    Gianni Ceresa Mod

    Hi,

    In your "Previous Year" calculation, what are you trying to get as value?

    Currently, if your formula would validate, your "Previous Year" calculation would only have a TRUE or FALSE value, because the calculation expression is a logical operation.

    Shouldn't your "Previous Year" calculation be just the @parameter("…")(2024) - 1 ? So the calculation will have a value being a number, and you can then use it to filter a column by it etc.

  • Gianni Ceresa
    Gianni Ceresa Mod
    edited December 2024

    Actually I believe you also have an error in the formula you used:

    EXTRACTYEAR returns a timestamp for January 1st for the year in which the input value occurs.

    That's not what you want, you want just the year, as a number (and not a timestamp), for your date: you should use the YEAR(<date>) function instead.

  • Kenneth_W
    Kenneth_W Rank 1 - Community Starter

    Hi Gianni

    Thanks for prompt reply.

    What I am trying to retrieve is

    There are 4 purchase order records:

    #1 PO Date=2024/11/1; Price=$100; Supplier=ABC
    #2 PO Date=2024/12/1; Price=$90; Supplier=XYX
    #3 PO Date=2023/11/1; Price=$60; Supplier=ABC
    #4 PO Date=2023/12/1; Price=$70; Supplier=XYX

    I would like to have dashboard to find out the latest and last year latest price.

    So I have created dashboard filter using PO Creation Date - Year to allow user to choose which year they would like to see.

    image.png

    I don't have the issue for the latest price of the selected Year. The Price will be changed based on why user select in the parameter.

    image.png

    However, for last year latest record. The filtering is not work as expected. What ever I choose, the filter just ignore it. In the filter, i just use parameter (…) - 1.

    image.png
    image.png
    image.png

  • Rajakumar Burra
    Rajakumar Burra Rank 6 - Analytics Lead

    This new filter has limitations. It works only for list filters.

    May be that is the reason for error.

    You can refer below thread.

    Parameter Binding option should be available for new Parameter - Derived dates filter. — Oracle Analytics