Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
How to properly write this syntax avg({<Location>} Sum({<[Type]>} Value))
The purpose is to get the AVERAGE, of the SUM of VALUE per TYPE, per LOCATION
Data structure looks like the following below
Location | Prod 1+2+3 | Prod 1 | Prod 2 | Prod 3 | Prod 4 | Prod 5+6 | Prod 5 | Prod 6 | Prod 7+8 | Prod 7 | Prod 8 | Prod 9+10 | Prod 9 | Prod 10 | Prod 11+12+13 | Prod 11 | Prod 12 | Prod 13 | Prod 14+15 | Prod 14 | Prod 15 |
A | 353.17 | 84.00 | 269.17 | 0.00 | 497.92 | 5,775.60 | 871.60 | 4,904.00 | 2,645.40 | 1,805.40 | 840.00 | 852.00 | 810.80 | 41.20 | 1,248.30 | 738.30 | 510.00 | 0.00 | 10.00 | 10.00 | 0.00 |
B | 76.02 | 17.00 | 59.02 | 0.00 | 648.80 | 1,150.40 | 1,068.40 | 82.00 | 0.00 | 0.00 | 0.00 | 25.40 | 0.00 | 25.40 | 430.80 | 430.80 | 0.00 | 0.00 | 20.00 | 20.00 | 0.00 |
C | 3.7 | 3.7 | 0 | 0 | 84.7 | 1960.9 | 1728.9 | 232 | 0 | 0 | 0 | 866.4333 | 555.6 | 310.8333 | 574.7 | 574.7 | 0 | 0 | 0 | 0 | 0 |
I just cant seem to get the right value
You cannot nest one aggregation function inside another, unless you use Aggr(). Aggr() is a for-next loop so you can specify "per what" you want to calculate the average.
Try e.g.
Avg(Aggr(Sum(Value),Location))
It will calculate the Sum(Value) for each Location, and then calculate the average of the resulting array.
See also https://community.qlik.com/t5/Design/Set-Analysis-in-the-Aggr-function/ba-p/1463822
You cannot nest one aggregation function inside another, unless you use Aggr(). Aggr() is a for-next loop so you can specify "per what" you want to calculate the average.
Try e.g.
Avg(Aggr(Sum(Value),Location))
It will calculate the Sum(Value) for each Location, and then calculate the average of the resulting array.
See also https://community.qlik.com/t5/Design/Set-Analysis-in-the-Aggr-function/ba-p/1463822
Try this
avg({<Location>} Aggr(Sum({<[Type]>} Value), Location, Type))
Hi @Veq21984,
Check this:
Avg(
Aggr(
Sum(Value),
[Type], Location
)
)
If above suggestions still not resolve your query, than come up with Input Data and out put you're looking at.