Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
sujeetsingh
Master III
Master III

hi,

Your expression is having a small error

it should be like this

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

View solution in original post

5 Replies
sujeetsingh
Master III
Master III

See this sample

Not applicable
Author

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

sujeetsingh
Master III
Master III

hi,

Your expression is having a small error

it should be like this

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

Not applicable
Author

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

Not applicable
Author

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