Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have created a straight table where the Dimension is a variable and controlled from a list box. Works great mostly.
I have a measure called Average Age that is Avg(Aggr(SUM(DISTINCT Age),PatientID)). 90% of the time it calculates correctly for each dimension value. In some cases it is 100% correct.
However, in a couple of cases it only calculates for a few of the Dimension values and then is blank for others. I have checked the raw data and I don't see any difference that would cause this.
If I just pick the dimension value that has no Age value showing then the correct age shows up. However, if I go back to displaying all of the values then it is blank.
I realize that this description is very high level and probably confusing.
Here is a more detailed example.
1. I pick Primary Program from the list box. The table refreshes and I have 4 rows (1 for each Primary Program). The Avg Age is displayed correctly for each row.
2. I pick Secondary Program from the list box. The table refreshes and I have 9 rows (1 for each Secondary Program). The Avg Age is correct for 8 of the rows. One row has the Avg Age blank. The raw data behind this looks fine. I see no difference between this row that is not working and the others that are working.
What dimensions do you have in your chart. Can you try this:
Avg(Aggr(NODISTINCT Sum(DISTINCT Age),PatientID))
UPDATE: If this doesn't work, would you be able to share a sample?
What dimensions do you have in your chart. Can you try this:
Avg(Aggr(NODISTINCT Sum(DISTINCT Age),PatientID))
UPDATE: If this doesn't work, would you be able to share a sample?
You might experience a dimensional grain mismatch between your chart dimensions and the dimension in your aggr() function:
Is your Secondary Program dimension maybe more granular than your PatientID dimension?
That worked. Thank you.