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…

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

Have you looked at this? [image] periodOverPeriodDifference for workdays Question & Answer 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…

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…

[image] 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 w…

[image] 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},[{cl…

@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 c…

Need to account for weekends in a date filter

Q&A

Max March 15, 2023, 9:10pm 2

Have you looked at this?

Topic		Replies	Views
periodOverPeriodDifference for workdays Q&A quick-sight , qls-basic-calc	3	585	February 9, 2023
How do I exclude weekends from the visuals? Q&A error , quick-sight , calculations , Business-Intelligence-Engineer	2	445	May 27, 2024
Pre-filter 30-day sum Q&A quick-sight , calculations	3	346	September 1, 2023
月間累計の営業日を表示させたい日本語で質問 \| Q&A analysis , quick-sight , Business-Intelligence-Engineer , Japanese	10	346	December 4, 2024
Calculated field syntax for Weekdays & Weekends Q&A quick-sight , calculations	1	561	April 27, 2023

Need to account for weekends in a date filter

Related topics