topic Aggr() aggravation in QlikView

Aggr() aggravation

Wed, 04 Nov 2015 23:38:24 GMT

I'm trying to build a measure to show the expected number of diseases in a local population with known age distribution (and where the disease rate varies by age). To do this, I multiply the population at each age group by the rates of disease at the national level, i.e.

Sum( Aggr( Sum( Population ) * Sum( DiseaseCases_National ) / Sum( Population_National ), AgeGroup )

To make this work on a chart with drilldowns for both location and disease type dimensions, I've had to add both the Location grain and the DiseaseType grain to the AgeGroup (see footnote for details).

This not only slowed things down significantly, but it's also technically wrong (or at least not to spec), since it means that location and disease type are being used as weighting variables - as opposed to being exploratory dimensions, i.e. the measure (expected cases) should be sliceable and collapsible by location and disease type.

Any workaround (or optimization) tips on this would be much appreciated.

Dannie

P.S. HIC provides an excellent discussion of this Aggr() function pitfall here:

https://community.qlik.com/blogs/qlikviewdesignblog/2015/10/05/pitfalls-of-the-aggr-function

Somehow I find it odd that Aggr() ignores row/col selections (or "outer aggregations", in Henric's terms). I would've thought this is the role of the "Total" qualifier, i.e. to sum across a dimension ignoring its filters or selections on the chart.

Re: Aggr() aggravation

jonathandienst — Thu, 05 Nov 2015 09:41:55 GMT

I assume you mean changing the expression to

=Sum(Aggr(Sum(Population) * Sum(DiseaseCases_National) / Sum(Population_National), AgeGroup, Location, DiseaseType))

Perhaps your need this:

=Sum(Aggr(Sum(Population) * Sum(DiseaseCases_National) / Sum(TOTAL <Location, DiseaseType> Population_National), AgeGroup, Location, DiseaseType))

This will not necessarily improve performance, but it should ensure that the more granular values will sum up to the original values.

Re: Aggr() aggravation

Thu, 05 Nov 2015 23:32:07 GMT

Not quite. I think you meant this:

= Sum(Aggr(Sum(Population) * Sum(TOTAL <Location, DiseaseType> DiseaseCases_National) / Sum(TOTAL <Location, DiseaseType> Population_National), AgeGroup, Location, DiseaseType))

... which yields the same results as this:

= Sum(Population) * Sum(DiseaseCases_National) / Sum(Population_National)

where the rate is not stratified by age. Below table illustrates the formula:

National National Local Local

Age Disease Population Population Disease Expected

------------------------------------------------------------------------------------------------------------------------------------

40 5 100 10 = (5/100)*10 0.5

50 10 200 20 = (10/200)*20 1.0

60 20 100 50 = (20/100)*50 10.0

------------------------------------------------------------------------------------------------------------------------------------

Expected Cases = 11.5

Again, ideally, this should work regardless of other dimensions on the chart, with only Age as the aggregation (or stratification) variable, i.e. = Sum(Aggr(Sum(Population) * Sum(DiseaseCases_National) / Sum(Population_National), AgeGroup))

But when I add location (or another non-age dimension) to the chart, the measure is calculated for only one subgroup, e.g.

Location Expected Cases

A 11.5

B -

C -

I suppose the behavior I'm looking for is similar to the following DAX formula in Excel PowerPivot:

= SUMX( SUMMARIZE( AgeDimension, AgeGroupKey, [Rate National] ), [Rate National]*[Local Population] )

which works within any dimension context on the pivot table or chart.