calculating average for each month and summing all calculated averages

[muhammadraza]

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
ADU	13-Jan	10,162,929
ADU	13-Jan	1,539,488
ADU	13-Jan	-
ADU	13-Jan	-
ADU	13-Jan	-
ADU	13-Jan	7,000,000
ADU	13-Jan	12,115,840
ADU	13-Jan	-
ADU	13-Jan	-
ADU	13-Jan	101,742
ADU	13-Jan	1,084,655
ADU	13-Jan	542,328
ADU	13-Jan	2,711,638
ADU	13-Jan	-
ADU	13-Jan	4,338,621
ADU	13-Jan	-
ADU	13-Jan	-
ADU	13-Jan	-
ADU	13-Jan	-
ADU	13-Jan	-
ALU	13-Jan	-
ALU	13-Jan	-
ALU	13-Jan	-
ALU	13-Jan	-
ALU	13-Jan	6,000
ADU	13-Feb	10,162,929
ADU	13-Feb	1,539,488
ADU	13-Feb	-
ADU	13-Feb	-
ADU	13-Feb	-
ADU	13-Feb	7,000,000
ADU	13-Feb	11,925,759
ADU	13-Feb	-
ADU	13-Feb	-
ADU	13-Feb	100,000
ADU	13-Feb	1,084,655
ADU	13-Feb	542,328
ADU	13-Feb	2,711,638
ADU	13-Feb	-
ADU	13-Feb	4,338,621
ADU	13-Feb	-
ADU	13-Feb	-
ADU	13-Feb	-
ADU	13-Feb	-
ADU	13-Feb	-
ALU	13-Feb	-
ALU	13-Feb	-
ALU	13-Feb	-
ALU	13-Feb	-
ALU	13-Feb	6,000
ADU	13-Mar	10,162,929
ADU	13-Mar	1,539,488
ADU	13-Mar	-
ADU	13-Mar	-
ADU	13-Mar	-
ADU	13-Mar	7,000,000
ADU	13-Mar	11,911,180
ADU	13-Mar	-
ADU	13-Mar	-
ADU	13-Mar	100,000
ADU	13-Mar	1,084,655
ADU	13-Mar	542,328
ADU	13-Mar	2,711,638
ADU	13-Mar	-
ADU	13-Mar	4,338,621
ADU	13-Mar	-
ADU	13-Mar	-
ADU	13-Mar	-
ADU	13-Mar	-
ADU	13-Mar	-
ALU	13-Mar	-
ALU	13-Mar	38,000
ALU	13-Mar	-
ALU	13-Mar	-
ALU	13-Mar	9,677

Regards,

Raza

[swuehl]

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]

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

[muhammadraza]

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

[techvarun]

which is the datasource currenthly you are using?

[muhammadraza]

the above provided xlsx file

[Gysbert_Wassenaar · Accepted solution]

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