Hi,
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 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 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(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
