Qlik Sense Aggr question:
Jessica Malenfant Aug 4, 2016 4:30 PMHi,
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 monthyear (variable = datemonth) so that i end up with a table like this:
Year Year(datemonth) works  Center_ID  # encounters by year for the center this is sum of countenc by year(datemonth) by center_id (can't get syntax)  

2010  1  290,626  
2011  1  318,819  
2010  2  147,780  
2011  7  182,268 
Year this works: year(datemonth)  Center_ID  Average # 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) 

2010  1  24,219  % 
2011  1  26,568  
2012  1  32,491  
2010  2  12,315 
Someone also suggested I create these as "master items" (the link icon in the edit app screen) so that these calculated fields can be reused 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 212 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(datemonth)countenc))
Avg(Aggr(SUM countenc),datemonth))
Aggr({SUM(countenc),Year(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

Encounter_Data_example.csv 83.5 K