Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
luca_bellotti
Contributor III
Contributor III

Week day year Average

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:

table.PNG

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

Result.PNG

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.

 

 

Labels (3)
10 Replies
zzyjordan
Creator II
Creator II

Hi, Luca

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

ZZ
luca_bellotti
Contributor III
Contributor III
Author

It doesn't work.

result1.PNG

rbartley
Specialist II
Specialist II

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.

 

Average2.PNG

 

Average.PNG

 

luca_bellotti
Contributor III
Contributor III
Author

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:

result1.PNG

What I need is the total average for eache  weekday.

rbartley
Specialist II
Specialist II

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.

rbartley
Specialist II
Specialist II

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.

AverageByDayIncCategory.PNG

 

luca_bellotti
Contributor III
Contributor III
Author

Here you can find my app.

rbartley
Specialist II
Specialist II

How about this? 

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

AvSalesWeekDayUsingCount.JPG

 

 

luca_bellotti
Contributor III
Contributor III
Author

No, the correct result should be this:

result1.PNG