5 Replies Latest reply: Jun 12, 2013 2:39 PM by matthew thompson

# Sum sales last weekday

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

• ###### Re: Sum sales last weekday

See this sample

• ###### Re: Sum sales last weekday

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

• ###### Re: Sum sales last weekday

hi,

Your expression is having a small error

it should be like this

=sum({\$<Day={\$(=weekday(today()-1))}>}Value)

• ###### Re: Sum sales last weekday

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

• ###### Re: Sum sales last weekday

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