Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I've been trying to write an expression to sum sales over the last weekday. So on Tuesday, I need to have the sum of all sales on Monday, etc. If I use the following set, I can sum Monday's sales:
Sum({<[Calendar Day Name] = {"Monday"} >} _f_OK_SIGNUP)
However, I cannot figure out how to create a dynamic set using the weekday function to sum the last weekday's sales. I've tried quite a few variations without success. Here's one:
sum({<[Calendar Day Name] = {"$(=num(weekday(today()-1))"} >} _f_OK_SIGNUP)
If I place =num(weekday(today(0)-1)) in a textbox it retruns 0, which is Monday, so I would have expected this to work.
Any help would be much appreciated.
Matt
hi,
Your expression is having a small error
it should be like this
=sum({$<Day={$(=weekday(today()-1))}>}Value)
See this sample
Thanks,
What I'm looking for is to make this dynamic so I'm always able to sum the last weekdays sales:
So if today was Tuesday, instead of this =sum({$<Day={'Mon'}>}Value) , I'd have something more like
=sum({$<Day={{"$(=num(weekday(today()-1))"} >}Value) . However, I can't get this option to work.
Any ideas?
Matt
hi,
Your expression is having a small error
it should be like this
=sum({$<Day={$(=weekday(today()-1))}>}Value)
Thanks, this works well and is correct, however it won't work in my app. I imagine it has something to do with my date formatting.
Any ideas or suggestions are welcome. My [Calendar Day Name] returns the full day name and when I run weekday(today()) I get an abbreviated name. Not sure if that's the issue, I tried this to work around it so both names matched, but still no luck:
=sum({$<[Calendar Day Name]={$(=date(weekday(today()-1)),'WWWW')}>}_f_OK_SIGNUP)
Any ideas how I can make this work?
Best,
Matt
I ended up shortening daynames on import to:
SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun';
This allowed the weekday function to return an equal match to my calendar day name field and it works now.
Thanks again for the help, much appreciated.
Matt