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:
# 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_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)|
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
% 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