Nested Aggregation Needed

[Egon]

Hi There,

I think I need nested aggregation to display something the way I want:

This gist is that I'm counting the number of dashboards a system has, and if it has one or more dashboards to indicate it with a 1, else it gets a zero.

I would then need to sum that to know how many of the systems within a team are running a dashboard. Conceptually, I would want to do something like this within my chart:

Sum(if(Count(dashboard) >= 1, 1, 0))

But of course this is nested aggregation and not appreciated by Qlik. Any way to get around it? If looked into Aggr() but can't seem to make it do what I need.

[marcus_sommer]

You may try it in this way:

if(Count(dashboard) >= 1, Sum(1))

- Marcus

[Egon]

That doesn't quite work. 

For reference I wrote something in the load editor that gives me the exact numbers I should be getting in the chart, as a double check.

Dont know what additional info I could give?

So I have "teams", and each team has a different amount of  "systems", and each system can have "dashboards". For each system I thus have to indicate if they have at least 1, and then count the amount of systems that have at least 1 dashboard for each team.

[marcus_sommer · Accepted solution]

It depends on your data, data-model and the dimensional context if an aggregation returned the expected value or not. Means if everything is properly associated and the chart contains all relevant dimensions the above mentioned approach should work within the most scenarios. If the object doesn't contained the right context you need to add it within the aggr(), for example with something like:

Sum(aggr(if(Count(dashboard) >= 1, 1, 0), Dim1, Dim2))

whereby Dim1 and Dim2 are just placeholder for the needed dimensionality - in your case probably Teams and System but maybe also others like any period-fields.

- Marcus