I want to calculate the Z-score for a list of hospital CMIs. The CMI is the sum of the relative weights for each DRG divided by the total number of discharges. The Z-score would subtract the mean from CMI for each provider and then divide that by the SD of CMI. I created three calculated fields. AVG CMI= avg(drg_weight), Stdevp CMI = Stdevp(CMI). Both of these calculated fields work fine. However, I’m getting an error on the third field. Z-score = ({provider_case_mix_index}-{AVG CMI}/{STdevp CMI}) The error message states, “Mismatched aggregation”. Custom aggregation can’t contain both aggregate AVG and non-aggregate AVG (drg_weights) in any combination. Can you tell me how to rewrite this to avoid the mismatched aggregation error? I want this calculation to be dynamic so that it update when we add new data each month.
Hello @smoore , welcome to the Quicksight community!
Is {provider_case_mix_index} a calculated field in your analysis? If not, I would try adding an aggregation to it that makes sense for your anticipated outcome. Then re-running the calculation. Something like:
sum({provider_case_mix_index})-{AVG CMI}/{STdevp CMI}
I put sum() as a placeholder for now. Let me know if this helps!
1 Like
Hi,
It is a calculated field.