Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
Try this:
Avg(Aggr(Only(Age), PatientID))
or
Avg(Aggr(Avg(Age), PatientID))
For patient count you can do this -> Count(DISTINCT PatientID)
Try this:
Avg(Aggr(Only(Age), PatientID))
or
Avg(Aggr(Avg(Age), PatientID))
For patient count you can do this -> Count(DISTINCT PatientID)
=Avg(Aggr(SUM(DISTINCT Age),PatientID))
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.
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?
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.