Need to account for weekends in a date filter


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?

Have you looked at this?

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.


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

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.

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