Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

QlikWorld Online 2021, May 10-12: Our Free, Virtual, Global Event REGISTER TODAY
Showing results for 
Search instead for 
Did you mean: 

Qlik Sense Aggr question:


I'm new to Qlik Sense and have spent hours trying to find a solution to this probably simple question:

I am trying to make a simple table(as a starting point)  based on the flat data table (attached) that has the following calculated fields:

# of encounters (variable = countenc) by site (variable = center_id) for each month-year (variable = datemonth) so that i end up with a table like this:



Year(datemonth) works


# encounters by year for the center

this is sum of countenc by year(datemonth) by center_id (can't get syntax)


Year this works: year(datemonth)Center_IDAverage # encounters by year for the center (total # of countenc for all datemonth rows for a year/ total # of rows for the center_id)% of encounters each year by center that have BMI data ((sum of countbmi / sum of countenc for each year for a center_id)*100)

Someone also suggested I create these as "master items" (the link icon in the edit app screen) so that these calculated fields can be re-used in different charts, etc.I'm simply trying them all out in a table as a Measure type of Column

Thank you for your note and sorry for the confusion. Here’s an update to what I am trying to do. Hopefully it makes sense this time:

Total  # of encounters by center by year: this would be calculated by the sum of countenc by year(datemonth) by center_id. I cannot figure out the syntax for this but I manually calculated the in the table below based on the sample spreadsheet

Average # of encounters by center by year: this would be calculated by the average countenc for year(datemonth) by center. For 2010 center 1, I used the average function in excel for column C, rows 2-12 of the sample spreadsheet.

I’ve been trying various combinations of this type of expression in Qlik, note that I did not include the center_id here because I just wanted to figure out how to get the average function to work first. I manually calculated the average countenc for 2010 in all centers, which is 12,099


Avg(Aggr(SUM countenc),datemonth))


Avg(Aggr({SUM countenc},Year(datemonth)))

% of encounters that have BMI data: I would like to get present this data by year and by center. This is done by calculating the % of countbmi /countenc

Thanks so much everyone!

Message was edited by: Jessica Malenfant

1 Reply

Hi Jessica,

Can you post what have you tried and what result that you expected to show from your flat file? So i can validate my expression.

# of encounters : i need to sum or count countenc? How to calculate # of encounters?

Total # of encounters for all datemonths row for a year : is it the same as # of encounters above?

Total # of rows for the center id : is it total rows based on center id? so in your case total data of center id = 1 is 66?