Qlik Community

Ask a Question

App Development

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

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Garfunkal
Contributor III
Contributor III

Row Level Aggregation

Hi - I've been looking at this for ages, but I've hit a brick wall...

I'm building something which takes historical data, creates a ratio, then applies that ratio to future metrics.   The pivot then displays the future metrics.  I can get the pivot to behave perfectly well at a Totals level, but they also want to it doing the aggregation at a lower level... basically where the calculation happens on a row by row basis, and then gets summed afterwards. 

 

This Measure is fine...

SUM( {< DataType={Forecast} >}
  AGGR(

(SUM(Total {<Year={$(vYearChoiceBaseCommission)} >}[Underlying Claims]) / SUM(Total{<Year={$(vYearChoiceBaseCommission)} >}[GEP]))
*

(SUM( GEP_Forecast))
, Year)
)

 

This is absolutely fine for working out all of the totals after aggregation, but I can't work out how to do the aggregation at a row level ( there is a split of 'GBMS' by Year).  I have been messing about with Removing Total, adding Aggr in different places, but i've got total blindness now!    I can't shove this in the script as they want other flexibility.... it's a whole thing...

Sadly, I am unable to export an app, but i've attached dummy data and a screenshot... please help! 

I'd really appreciate any help!

 

 

Labels (1)
1 Solution

Accepted Solutions
Garfunkal
Contributor III
Contributor III
Author

Hi - Solved it - if anyone else comes across the same, and what I assume is a very specific issue, then I needed to sub total my inner set analysis, and tweak the outer.  New code is: 

SUM( {< DataType={Forecast} >}

AGGR(

 

(SUM( Total <GBM> {<Year={$(vYearChoiceBaseCommission)} >}[Underlying Claims]) / SUM(Total <GBM> {<Year={$(vYearChoiceBaseCommission)} >}[GEP]))

*

(SUM({<DataType={Forecast}>}GEP_Forecast))

, Year, GBM )

)

 

View solution in original post

1 Reply
Garfunkal
Contributor III
Contributor III
Author

Hi - Solved it - if anyone else comes across the same, and what I assume is a very specific issue, then I needed to sub total my inner set analysis, and tweak the outer.  New code is: 

SUM( {< DataType={Forecast} >}

AGGR(

 

(SUM( Total <GBM> {<Year={$(vYearChoiceBaseCommission)} >}[Underlying Claims]) / SUM(Total <GBM> {<Year={$(vYearChoiceBaseCommission)} >}[GEP]))

*

(SUM({<DataType={Forecast}>}GEP_Forecast))

, Year, GBM )

)

 

View solution in original post