Expression Help

[rittermd]

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.

[sunny_talwar · Accepted solution]

Try this:

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

or

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

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

[MK_QSL]

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

[rittermd]

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.

[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?

[rittermd]

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.