Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Sum rows where I only want max amt for dimension combinations

I need to sum a field 'Patient' for all records for a time period. However, the Patients amount is repeated when there are multiple records referencing the same different combinations of dimensions. In other words, in the example below, I only want to get the max number of Patients (1000) when the record is repeated as follows:

Date, Patients, Office, AvgCost

Jan, 1000, XRAY, $2500

Jan, 1000, XRAY, $10500

Jan, 1000, XRAY, $5000

2 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

If I understand you correctly, you need to use an Advanced Aggregation function ARRG(), to calculate max amounts by Date and Office, and then summarize the results:


= SUM( AGGR( MAX(Patients), Date, Office))


Not applicable
Author

Awesome...that worked! What if I wanted to show that aggregate on every row in a table?