5 Replies Latest reply: Apr 28, 2016 11:00 AM by Mark Ritter RSS

    Expression Help

    Mark Ritter

      Here is my situation.

      I have patient data.  Each row of data is a therapy session.  So for example one patient could have 20 rows and another could have 3 rows of data.

      Each row contains their age.  I need to show the average age in my charts.  If I take the average of the sum of the 23 rows then the result will be skewed because of the patient with more rows of data.

      Patient 1 with 20 rows is 99 years old.

      Patient 2 with 3 rows is 50 years old.


      The average age should be 74.5.  If I do a Avg of the Age field then I get 92.60.


      I should also show a patient count of only 2.


      I have tried a bunch of things and nothing has worked so far.

        • Re: Expression Help
          Sunny Talwar

          Try this:


          Avg(Aggr(Only(Age), PatientID))




          Avg(Aggr(Avg(Age), PatientID))


          For patient count you can do this -> Count(DISTINCT PatientID)

            • Re: Expression Help
              Mark Ritter

              That sort of works.


              In this chart I have created a dimension that is based on the selection from a listbox.  So depending on what they select it shows a different set of dimension values with the metrics for that value.


              Is it possible to modify your expression to also Aggr based on the selected dimension using a variable?


              The variable I have is vDataDisplay and is equal to =$(='[' & only(Dim1) & ']')


              Dim1 comes from an inline table based on the selection from the listbox.


              I really hope the answer is Yes.

                • Re: Expression Help
                  Sunny Talwar

                  I am really sure what you mean. We just want a single age per patient id. Why would be want to add another dimension here? I would think that this Avg should be working as is. Can you share a sample to show where it isn't working?

                    • Re: Expression Help
                      Mark Ritter

                      I agree.  I would think that it would be working also.  But when I pick one of the dimension values the ages do not work correctly.  They seem to work in the others.  So it is most likely a data issue.  I need to dig further before I bother you with this any further.

                      Thanks for your help so far.

                • Re: Expression Help
                  Manish Kachhia

                  =Avg(Aggr(SUM(DISTINCT Age),PatientID))