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

    Qlik Sense Aggr question:

    Jessica Malenfant

      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:
          MARCO HADIYANTO

          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