7 Replies Latest reply: Apr 27, 2012 2:51 AM by matthew thompson

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

• Average sales per weekday

Hi,

just add a second set expression like

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

greets,

Max

• Average sales per weekday

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

• Average sales per weekday

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.

• Average sales per weekday

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

• Average sales per weekday

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

• Average sales per weekday

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

• Average sales per weekday

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