Hi,
I have a data set that is grouped as follows:
Measure ID, Provider ID, Location ID, Cost Center ID, Patient ID, Score
For each Measure ID / Provider ID / Location ID / Cost Center ID / Patient ID there may be multiple scores (scores are always 0 or 1, where 0 = Fail and 1 = Pass).
I have created a pivot table chart (chart #1) that correctly aggregates the number of passing scores by Measure ID and Location ID. For example, the chart looks like this:
Measure = P101, Location ID = 113, Passing Scores = 10,559
Measure = P101, Location ID = 116, Passing Scores = 5,060
Measure = P101, Location ID = 141, Passing Scores = 15,369
The expression I am using in the chart is as follows:
Sum({$<_PROVIDER_ID_PERFORMING = , _LOC_ID = P(_LOC_ID)>} Aggr(Max({$<_PROVIDER_ID_PERFORMING = , _LOC_ID = P(_LOC_ID)>} [Measure Score]), _LOC_ID, _MEASURE_ID, _PATIENT_ID))
The reason for the P() function is that if someone picks a provider ID from a dimension list box I only want to show the locations associated with this provider. Without the P() function my location chart ends up showing all locations. This works great at all levels of the drill.
Now, I have another pivot table chart (chart #2) that correctly aggregates the number of passing scores by Measure ID, Provider ID, and Location ID. For example, the chart looks like this when you select provider ID 117:
Measure ID = P101, Provider ID = 117, Location ID = 113, Passing Scores = 4
Measure ID = P101, Provider ID = 117, Location ID = 116, Passing Scores = 82
Measure ID = P101, Provider ID = 117, Location ID = 141, Passing Scores = 111
The expression that populates this chart is similar:
Sum(Aggr(Max([Measure Score]), _PROVIDER_ID_PERFORMING, _MEASURE_ID, _PATIENT_ID))
Again, this looks correct at all levels of the drill.
Now, what I would like to do is add a second expression to this provider chart (chart #1) that compares their number of passing scores to the total number of passing scores for that location. It should look like this:
Measure ID = P101, Provider ID = 117, Location ID = 113, Provider Passing Score s= 4, Location Passing Scores = 10,559
Measure ID = P101, Provider ID = 117, Location ID = 116, Provider Passing Scores = 82, Location Passing Scores = 5,060
Measure ID = P101, Provider ID = 117, Location ID = 141, Provider Passing Scores = 111, Location Passing Scores = 15,369
Adding my first expression (above) to this second chart doesn't work. I also tried using the Total keyword in the expression, but to no avail. In either instance my chart looks something like this:
Measure ID = P101, Provider ID = 117, Location ID = 113, Provider Passing Score s= 4, Location Passing Scores = 4
Measure ID = P101, Provider ID = 117, Location ID = 116, Provider Passing Scores = 82, Location Passing Scores = 84
Measure ID = P101, Provider ID = 117, Location ID = 141, Provider Passing Scores = 111, Location Passing Scores = 111
I appear to be getting some sort of different aggregation (note the difference in provider and location scores for location 116) but no where near the actual values I need. Here is the latest expression I have used - I've tried various iterations of it:
Sum(Total <_MEASURE_ID, _LOC_ID> Aggr(Max([Measure Score]), _LOC_ID, _MEASURE_ID, _PATIENT_ID))
If I add the set analysis from the expression in chart #1 to the above code and add that as a second expression to chart #1 I get the correct answer. However, when I remove the set analysis and move the expression to chart #2 it fails.
Does anyone have any tips on how to get this to work? I've just started to understand how the mysterious AGGR() function works so perhaps my problem lies there. Any help would be greatly appreciated. I can provide the .QVW I am working with if that helps. There is no private patient information in this data set, just internally-generated ID numbers.
Thanks,
Chris
