topic Re: Week day year Average in App Development

Week day year Average

luca_bellotti — Sat, 16 Nov 2024 06:52:36 GMT

Hello everyone,

I have the following tables:

Sales:
LOAD

ID,
Shop,
Category,
Date,
Sales;

Calendar:

LOAD

Date,
WeekDay,
NrWeek;

What I need to get is an average per week day of the whole year.

In the app I've created a table to check everyday if some sales are missing from the dataset:

Aside I created another table where I would like to have the average per week day:

AvgSales = Avg(Aggr(Sum(Sales),WeekDay)

As you can see this is not the average but the sum.

What can I do to get the average?

Thanks in advance.

Re: Week day year Average

zzyjordan — Fri, 11 Jan 2019 00:42:59 GMT

Hi, Luca

try this
AvgSales = Avg(Aggr(Sum(Sales),Date)

ZZ

Re: Week day year Average

luca_bellotti — Fri, 11 Jan 2019 08:22:03 GMT

It doesn't work.

Re: Week day year Average

rbartley — Fri, 11 Jan 2019 09:13:27 GMT

Hi Luca,

Perhaps I'm missing something, but if you only display the WeekDay and Sales in the table, all you need is to us Avg(Sales), no need for the Aggr() function.

Re: Week day year Average

luca_bellotti — Fri, 11 Jan 2019 11:05:25 GMT

Hi Rbartley,

the problem is that the db has sales for every category of every shop in everyday, so if I do a simple average it shows the average of category per day:

What I need is the total average for eache weekday.

Re: Week day year Average

rbartley — Fri, 11 Jan 2019 11:16:06 GMT

Ok, could you please post your app (remove of obfuscate any sensitive data if necessary)? You will need to zip it in order to post it as there is a bug on the Qlik Community site that prevents one from uploading the qvf files.

Re: Week day year Average

rbartley — Fri, 11 Jan 2019 11:31:42 GMT

Take a look at the attached app (you'll have to unzip it). I've included category in the data and if I use Avg(Sales) with just WeekDay in the table, the formula still works since Qlik Sense calculates it on the context of the dimensions in the table. I've also added another table which also includes category and in which I've used the formula: Aggr(Avg(Sales),WeekDay). This shows the same average figures too.

Re: Week day year Average

luca_bellotti — Fri, 11 Jan 2019 12:32:52 GMT

Here you can find my app.

Re: Week day year Average

rbartley — Fri, 11 Jan 2019 13:55:58 GMT

How about this?

=Aggr(Sum(Sales),WeekDay)/Count(WeekDay)

Re: Week day year Average

luca_bellotti — Fri, 11 Jan 2019 14:39:44 GMT

No, the correct result should be this:

Re: Week day year Average

rbartley — Fri, 11 Jan 2019 14:53:54 GMT

If I export the data to Excel and create a pivot table that includes the WeekDay and Average of Sales, the results are as follows (the same as in my previous post). So how exactly are you calculating the average?