Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

muhammadraza
Contributor

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

Re: calculating average for each month and summing all calculated averages

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
6 Replies
MVP
MVP

Re: calculating average for each month and summing all calculated averages

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
Valued Contributor

Re: calculating average for each month and summing all calculated averages

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

Highlighted
muhammadraza
Contributor

Re: calculating average for each month and summing all calculated averages

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
Valued Contributor II

Re: calculating average for each month and summing all calculated averages

which is the datasource currenthly you are using?

muhammadraza
Contributor

Re: calculating average for each month and summing all calculated averages

the above provided xlsx file

Re: calculating average for each month and summing all calculated averages

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
Community Browser