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?

@Max I do want Friday. When today = monday, sum Fri/Sat/Sun, else return yesterday.

friday = 1
saturday = 3
sunday = 0
monday = 4
tuesday = 6
wednesday = 8
thursday = 2

on monday, the field should show 4. on every other day the field should show the prior day’s value.

I’ve found that this correctly calculates previous day:

But i cannot yet get that to work with any kind of ifelse statement due to QuickSight not allowing nested aggregation.

So, closer, perhaps.


Can you wrap it in an ifelse and use firstValue and then depending on the value change the look back amount?

ifelse(firstValue(extract('WD',{date}),[{date} ASC],[{date}])=1