Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Tags (2)
7 Replies
Not applicable

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

Not applicable

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



masha-ecraft
Contributor

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.

Not applicable

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

Not applicable

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

Not applicable

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

Community Browser