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

Average sales per weekday

Hi,

I'm creating a daily sales report and need to compare today's sales against the same day's average over the last 4 weeks. For instance, today I would show Wednesday's sales, the average of the last 4 Wednesday's sales and the difference between them. I'm currently having issues getting Wednesday's sales to show. I'm using the following set:

sum({<[Day Name] = {'$(=weekday(today(0))'}>} [Sales])

If  I replace the above with today's day, it works fine, but I need something dynamic:

sum({<[Day Name] = {"Wednesday"}>} [Sales])


Once I get this sorted, I'll need to sum the sales from the last 4 Wednesdays. Does anyone know an expression for this?

Any insight would be appreciated. I've attached an example below.

Best,

Matt

7 Replies
Not applicable
Author

Hi,

just add a second set expression like

sum({$< SalesDate = {'>=$(=AddMonths(Now(0), -1))'}[Day Name] = {'$(=weekday(today(0))'}>} [Sales])

greets,

Max

Not applicable
Author

Thanks Max, that's very helpful. I still cannot get this part of the expression to work though:

[Day Name] = {'$(=weekday(today(0))'}

If I subsitute {'$(=weekday(today(0))'} for  {"Wednesday"} it works, but I need it to work dynamically. Does anything stand out to you as wrong with this expression?

Thanks again for the help.

Matt



masha-ecraft
Partner - Creator
Partner - Creator

Check the result of your formula weekday(today(0)) in a text box. If it does not return 'Wednesday', modify environment variable DayNames in the load script.

Not applicable
Author

It could be caused by the format within your [Day Name] column. Give it a try with num(weekday(today(0))) or alternatively text(weekday(today(0)))

How is your column populated ? If its plain text, make sure that the weekday function returns the same day name format - including probably abbreviations

Not applicable
Author

Thanks a lot for the help, really appreciate it. I still had issues getting the weekday expression to work. In the end, I've settled on the following solution to calculate yesterday's sales against the  average for last 4 like weekdays:

(sum({$< [Transaction Date] = {'$(=date(today(0)-8))'}>} [Sales]) +

     sum({$< [Transaction Date] = {'$(=date(today(0)-15)'}>} [Sales]) +

     sum({$< [Transaction Date] = {'$(=date(today(0)-22))'}>} [Sales]) +

     sum({$< [Transaction Date] = {'$(=date(today(0)-29))'}>} [Sales]) )/4

Best,

Matt

CELAMBARASAN
Partner - Champion
Partner - Champion

Hi,

     Do you have day name field?if so check with this

     Sum({<DayField={'$(=WeekDay(Today()))'},[Transaction Date]={">=$(=Date(today()-29))<=$(=Date(Today()-6))"}>} Sales)/4

To simplify yours as one expression you can write like this

sum({$< [Transaction Date] = {'$(=date(today(0)-8))','$(=date(today(0)-15)','$(=date(today(0)-22))','$(=date(today(0)-29))'}>} [Sales])/4

Celambarasan

Not applicable
Author

Thanks a lot for the help Celambarasan. Those queries are much cleaner/lighter than mine, however, I couldn't get either to work so will stick with what I have.

Thanks again.

Matt