Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Hi,
just add a second set expression like
sum({$< SalesDate = {'>=$(=AddMonths(Now(0), -1))'} , [Day Name] = {'$(=weekday(today(0))'}>} [Sales])
greets,
Max
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
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.
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
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
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
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