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

Filter Non-Measure Column

Received Response
41
Views
4
Comments
Vismay Barot
Vismay Barot Rank 4 - Community Specialist
in Oracle Analytics Cloud and Server

Greetings, so my Data looks somewhat like this:

image.png

with 1 FAW_DATE for 1 YEAR,

Now I'm trying to put FAW_DATE into workbook via simple calculation, it's throwing this error:

image.png

I understand the logic behind the same, but what I expect is to show "Month" out of FAW_DATE where Year = '2024'.

Your insights will be highly appreciated 😊

Answers

  • SteveF-Oracle
    SteveF-Oracle Mod

    When you type the function in the search it explains how the function works with an example.

    Date is not a measurement (measure).

    This function generates a new filtered measure from the given measure and the given filter expression.

    FILTER(measure USING filter_expr)

    measure is the name of a measure column. filter_expr is a filter expression.

    Example: FILTER(revenue USING brand = 'abc')

    image.png

    Do you need a calculation for this, or just add your date and create filter on it?

    Other comments, welcomed.

  • Gianni Ceresa
    Gianni Ceresa Mod
    edited Aug 14, 2024 3:14PM

    Hi,

    What should that calculation return for records with a date in 2023?

    You could use CASE WHEN "Year" = '2024' THEN MONTH("FAW_DATE") END

    But that calculation will return NULL for any other date not matching the year = 2024.

  • Vismay Barot
    Vismay Barot Rank 4 - Community Specialist

    You're right @SteveF-Oracle , as I said I understand how the formula works, and "FAW_DATE" isn't a Measure, basically I'll use Month(FAW_DATE) in a Formula.

    @Gianni Ceresa , this is the Final Formula:

    FILTER(MONTH(FAW_DATE) using YEAR = YEAR(@parameter("To Date")(Date '2024-01-01')))

    Just to decipher it to you, End User will select a "To Date" from Filter, then Year has to extracted from that date. The extracted year needs to be compares to Year Column in 1st Snapshot to reach to Date that will output Month.

    In simple SQL Language, I would like to:
    select Month(FAW_DATE) where Year = Year (Selected-Date) from Quota

    I hope this make sense to you.

    Also, I tried using FILTER(MAX(MONTH(FAW_DATE)) using YEAR = YEAR(@parameter("To Date")(Date '2024-01-01')))

    But what it basically does is take Maximum of FAW_DATE without filtering Year='2024'. For example, it return 10 in 1st snapshot scenario, rather than 3.

  • Gianni Ceresa
    Gianni Ceresa Mod

    Do not mix the 2 key concepts: a calculation and a filter.

    If you want to filter YEAR = YEAR( your parameter ), then do this as a filter: it can be a filter in the workbook, it can be a filter in the visualization where you need it.

    Then if you need to extract the month from a date, you can do that with a calculation.

    But you can't really "filter" the dataset exposed to a workbook via formulas based on a parameter: it would return you values when matching your filter, NULLs when not matching the filter (and NULL is still a value ! ).