Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

New Contributor III

Help with nested aggregation

Hello!  I'm trying to figure out how to modify this expression so that qliksense accepts the formula (as nested aggregation is not allowed).  Basically, if an average of scores is greater than or equal to 3, I want that to generate a count of "1", and then be able to sum those 1s (so that this will translate to a pivot format).  Here's my formula:

Sum(IF(Avg({$<[ELPScores.Concept]={'1.1*','1.2*','2.1*','2.2*','2.4*','3.1*','4.2*'}>} IndicatorScores)>=3.0,1))

Thanks for the help!


2 Replies
New Contributor III

Re: Help with nested aggregation

Wait- I think I may have got it after reviewing some other articles...!

SUM(IF(aggr(Avg({$<[ELPScores.Concept]={'1.1*','1.2*','2.1*','2.2*','2.4*','3.1*','4.2*'}>} IndicatorScores)>=3.0,Key_UserID),1))

Thanks Qlik community!

Re: Help with nested aggregation

You need to Add Aggr() between your two aggregating functions (Sum and Avg) with all your pivot table dimensions.


If(Avg({$<[ELPScores.Concept]={'1.1*','1.2*','2.1*','2.2*','2.4*','3.1*','4.2*'}>} IndicatorScores)>=3.0,1), <PivotTableDimensionsHere>))