Need to account for weekends in a date filter

[jayrey48]

Hello.

I have a collection of gauges that need to display the previous day’s metrics. This is fine using a filter set to “previous day” except for Monday, when I need to show a sum of Friday, Saturday and Sunday’s numbers.

I have been playing around with parameters and filters and the “addDateTime” and “addWorkDays” functions but nothing’s really working. Is this even possible?

[Max]

Have you looked at this?

periodOverPeriodDifference for workdays

Hi Thank you for the logic. But I am getting error when I tried the below formula ifelse (extract(‘WD’, {Range_Date})=2, periodOverPeriodDifference(max({Range_Count}),{Range_Date},‘DAY’,3), periodOverPeriodDifference(max({Range_Count}),{Range_Date},‘DAY’,1)) Error message “Mismatched aggregation. Custom aggregations can’t contain both aggregated and nonaggregated fields, in any combination.” at periodOverPeriodDifference(max({Range_Count}),{Range_Date},‘DAY’,3), Can you please help me un…

[jayrey48]

Interesting - I was also able to extract the day from the date to be able to know when a date was a Monday. The issue I’m still having is how to sum Fri, Sat and Sun data when it is a Monday.

[Max]

Hmm,

I might look to do this.

ifelse(extract(‘WD’,{arrival_timestamp})=2,sumOver(ifelse(extract(‘WD’,{arrival_timestamp})=1 OR extract(‘WD’,{arrival_timestamp})=6 OR extract(‘WD’,{arrival_timestamp})=7,{int bearable},0),[{client_id[users]},truncDate(‘WK’,{arrival_timestamp})],PRE_AGG),sumOver({int bearable},[{client_id[users]},truncDate(‘DD’,{arrival_timestamp})],PRE_AGG))

Essentially it will see if it’s a Monday then sum over your field if it’s a weekend (sun,sat,fri) partitioned by the week. Else it will sumOver your field partitioned by the day.

Let me know if that works

[jayrey48]

@Max

sumOver

It almost works… I am continuing to fiddle with the extraction of weekday vs. weeks in the ifelse statement. Since I’m looking back when current day = 2, I need day 6&7 from previous week and 1 from current week and that’s proving to be tricky.

Also trying a few things with periodToDateSumOverTime but that might not allow the nesting I appear to need.

[Max]

@Max

ifelse(extract(‘WD’,{arrival_timestamp})=2,sumOver(ifelse(extract(‘WD’,{arrival_timestamp})=1 OR extract(‘WD’,{arrival_timestamp})=6 OR extract(‘WD’,{arrival_timestamp})=7,{int bearable},0),[{client_id[users]},truncDate(‘WK’,{arrival_timestamp})],PRE_AGG),sumOver({int bearable},[{client_id[users]},truncDate(‘DD’,{arrival_timestamp})],PRE_AGG))

In this I am also taking in Friday. Have you taken that out?