Filter function for date condition error in OAC calculation

Kirk Li

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?)

Thanks for any advices!

Chere-Oracle

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.

Ram-Oracle

@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

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.

Chere-Oracle

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

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?

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

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.

DS_2024_WorkDays.xlsx

Uploaded Nov 5, 202419.23 KB

2.PO Create Date example:

DS_PO_CreateDate.xlsx

Chere-Oracle

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

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!

Date Blending: