1 Reply Latest reply: Aug 4, 2016 4:30 PM by Jessica Malenfant

Qlik Sense Aggr question:

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)

20101290,626
20111318,819
20102147,780
20117182,268

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)
2010124,219%
2011126,568
2012132,491
2010212,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

• Re: Qlik Sense Aggr question:

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?

@

Regards