topic Average sales per weekday in QlikView

Average sales per weekday

Wed, 25 Apr 2012 06:50:52 GMT

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

Wed, 25 Apr 2012 07:07:22 GMT

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

Wed, 25 Apr 2012 07:38:16 GMT

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

masha-ecraft — Wed, 25 Apr 2012 07:50:23 GMT

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

Wed, 25 Apr 2012 07:51:27 GMT

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

Wed, 25 Apr 2012 10:20:17 GMT

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

CELAMBARASAN — Wed, 25 Apr 2012 12:37:53 GMT

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

Fri, 27 Apr 2012 06:51:05 GMT

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