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: 
muhammadraza
Partner - Creator
Partner - Creator

calculating average for each month and summing all calculated averages

Hi guyz,

I have the following data and I want to calculate average for each month for specific (or selected) company/ies and display the total of averages.

I have tried avg(aggr(sum(Value),Company, Month(Period))) but I am not getting the desired result. Please guide..

Company Period Value
ADU13-Jan10,162,929
ADU13-Jan1,539,488
ADU13-Jan -  
ADU13-Jan -  
ADU13-Jan -  
ADU13-Jan7,000,000
ADU13-Jan12,115,840
ADU13-Jan -  
ADU13-Jan -  
ADU13-Jan101,742
ADU13-Jan1,084,655
ADU13-Jan542,328
ADU13-Jan2,711,638
ADU13-Jan -  
ADU13-Jan4,338,621
ADU13-Jan -  
ADU13-Jan -  
ADU13-Jan -  
ADU13-Jan -  
ADU13-Jan -  
ALU13-Jan -  
ALU13-Jan -  
ALU13-Jan -  
ALU13-Jan -  
ALU13-Jan6,000
ADU13-Feb10,162,929
ADU13-Feb1,539,488
ADU13-Feb -  
ADU13-Feb -  
ADU13-Feb -  
ADU13-Feb7,000,000
ADU13-Feb11,925,759
ADU13-Feb -  
ADU13-Feb -  
ADU13-Feb100,000
ADU13-Feb1,084,655
ADU13-Feb542,328
ADU13-Feb2,711,638
ADU13-Feb -  
ADU13-Feb4,338,621
ADU13-Feb -  
ADU13-Feb -  
ADU13-Feb -  
ADU13-Feb -  
ADU13-Feb -  
ALU13-Feb -  
ALU13-Feb -  
ALU13-Feb -  
ALU13-Feb -  
ALU13-Feb6,000
ADU13-Mar10,162,929
ADU13-Mar1,539,488
ADU13-Mar -  
ADU13-Mar -  
ADU13-Mar -  
ADU13-Mar7,000,000
ADU13-Mar11,911,180
ADU13-Mar -  
ADU13-Mar -  
ADU13-Mar100,000
ADU13-Mar1,084,655
ADU13-Mar542,328
ADU13-Mar2,711,638
ADU13-Mar -  
ADU13-Mar4,338,621
ADU13-Mar -  
ADU13-Mar -  
ADU13-Mar -  
ADU13-Mar -  
ADU13-Mar -  
ALU13-Mar -  
ALU13-Mar38,000
ALU13-Mar -  
ALU13-Mar -  
ALU13-Mar9,677

Regards,

Raza

1 Solution

Accepted Solutions
Gysbert_Wassenaar

You need to remove month from the aggr function: sum(Aggr(avg([O/S Avg]),Company)). See attached example.


talk is cheap, supply exceeds demand

View solution in original post

6 Replies
swuehl
MVP
MVP

You can't use a calculated dimension in your advanced aggregation (i.e. Month(Period) is not allowed), so try

avg(aggr(sum(Value),Company, Period))

Then I am not sure what you want to calculate, the average over all monthly Company Values, or the sum of averages? What is your expected outcome (a small table showing this will probably help)?

jolivares
Specialist
Specialist

Try to make a table about you want with this set of data...

muhammadraza
Partner - Creator
Partner - Creator
Author

Hi, Please find the attached files xlsx datasource and qlickview document, in the attached xlsx file I have done the calculation manually, basically this is the result which I want to achieve in the "Total" of "OS/WA" column (as shown in the below image) but I am not getting the desired result by "=sum(Aggr(avg([O/S Avg]) ,Month,Company))" formula

image.png

techvarun
Specialist II
Specialist II

which is the datasource currenthly you are using?

muhammadraza
Partner - Creator
Partner - Creator
Author

the above provided xlsx file

Gysbert_Wassenaar

You need to remove month from the aggr function: sum(Aggr(avg([O/S Avg]),Company)). See attached example.


talk is cheap, supply exceeds demand