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 function for date condition error in OAC calculation

Accepted answer
109
Views
7
Comments
Kirk Li
Kirk Li Rank 5 - Community Champion
in Oracle Analytics Cloud and Server

Hello Group!

I use the Filter function in OAC calculation,and use Date Related fields as the filter expression,

but it shows the below error message,please help!

Or any suggestions if I want to find out a measure fields on a specific date. (For eg, Logic SQL or other functions should I use?)

image.png

Thanks for any advices!

Tagged:

Best Answer

  • Chere-Oracle
    Chere-Oracle Rank 5 - Community Champion
    edited Nov 5, 2024 11:04PM Answer ✓

    May I suggest: FILTER( WorkDayAccumulated USING( CRTD = SysDate ))

    or

    FILTER( WorkDayAccumulated USING( Date = SysDate ))

    Since CRTD and Date are joined, either one should work OK. I assume your SysDate calculation result is in the correct time zone. Also consider using Current_Date instead of SysDate.

Answers

  • Ram-Oracle
    Ram-Oracle Rank 6 - Analytics Lead

    @Kirk Li please pull crtd ,rsum and filter calculated column and create tabular view and share the screenshot.

    could you also share filter calculation logic.

  • Kirk Li
    Kirk Li Rank 5 - Community Champion
    edited Nov 5, 2024 12:58PM

    Thanks,Ram!

    I use 2 Data set one for PO List, and the other for WordDay calculation:

    For the filter calculation, I try to find the Rsum value from the DS_WorkDate_1105 by the system date.

    The purpose is to calculate the workdays between the PO CreateDate and the System date.

    image.png image.png image.png
  • Chere-Oracle
    Chere-Oracle Rank 5 - Community Champion

    This doesn't answer your question, but I'm not sure a running sum will work properly in a filter function because — I believe — running sum calculates using filtered rows.

  • Kirk Li
    Kirk Li Rank 5 - Community Champion

    Hi Chere,thanks for you advice,

    not sure if I adjust as below,

    is it possible to use logic sql or Filter function to find the Current Date's WorkDayAccumulated from DataSet 1. DS_2024_Workdays?

    image.png

    My purpose is to calculate the working days between Current Date and each PO's Create Date like this formula:

    image.png

    So I adjust the Data set as below outside OAC for the working days for each calendar date in 2024:

    1.DS_2024_Workdays: for every WorkDay in 2024.

    image.png
    DS_2024_WorkDays.xlsx

    2.PO Create Date example:

    image.png
    DS_PO_CreateDate.xlsx
  • Chere-Oracle
    Chere-Oracle Rank 5 - Community Champion
    edited Nov 5, 2024 7:28PM

    Hi Kirk Li,

    I think the measure will work well in your filter if you perform the WorkDayAccumulated (i.e., Rsum) calculation within the dataset, but not if you perform the calculation within the DV workbook.

    I have two questions regarding the USING() part of your FILTER() function:

    1. Why are you filtering against DATE(DAY) instead of CRTD?
    2. What is the calculation in your "filter" calculated column?
  • Kirk Li
    Kirk Li Rank 5 - Community Champion

    Hi Chere,

    1.Why are you filtering against DATE(DAY) instead of CRTD?

    I'd like to select the WorkDayAccumulated column value which Date is equal to Current Date.

    Sorry I may misunderstanding the Filter Function,

    please suggest,

    thanks!

    image.png

    Date Blending:

    image.png