Weird Expression Behavior

[rittermd]

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.

[sunny_talwar · Accepted solution]

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?

[swuehl]

You might experience a dimensional grain mismatch between your chart dimensions and the dimension in your aggr() function:

Pitfalls of the Aggr function

Is your Secondary Program dimension maybe more granular than your PatientID dimension?

[rittermd]

That worked.  Thank you.