Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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
Honored Contributor III

Re: Sum sales last weekday

hi,

Your expression is having a small error

it should be like this

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

5 Replies
sujeetsingh
Honored Contributor III

Re: Sum sales last weekday

See this sample

Not applicable

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

sujeetsingh
Honored Contributor III

Re: Sum sales last weekday

hi,

Your expression is having a small error

it should be like this

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

Not applicable

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

Not applicable

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

Community Browser