Need to account for weekends in a date filter

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?

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.

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

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:
periodOverPeriodLastValue(sum({value}),{date},DAY,1)

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

So, closer, perhaps.

@jayrey48

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
,periodOverPeriodLastValue(sum({value}),{date},DAY,3)
,periodOverPeriodLastValue(sum({value}),{date},DAY,1))