Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
You need to remove month from the aggr function: sum(Aggr(avg([O/S Avg]),Company)). See attached example.
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)?
Try to make a table about you want with this set of data...
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
which is the datasource currenthly you are using?
the above provided xlsx file
You need to remove month from the aggr function: sum(Aggr(avg([O/S Avg]),Company)). See attached example.