I have a straight table with 3 dimensions (State, Hospital, Nursing Unit). These dimensions act as Russian Nesting Dolls. Nursing Units make up a Hospital and all Hospitals are included within States. Just like you would expect. I want an expression that gives the total top responses for the hospital and an expression for the nursing units.
State Hospital Nursing Unit Hospital Top Responses Nursing Unit Top Responses
AL A 1 30 10
AL A 2 30 5
AL A 3 30 15
CA B 1 100 99
CA B 2 100 1
Here's the code that I'm using to generate the "Hospital Responses" column;
=(sum(aggr(count( {$<Answer = {1} ,NursingUnit=>} SurvID ),Hospital)))
Where "Answer = 1" gives me the Top responses instead of other responses and "Nursing Unit=" SHOULD, in theory, skip the NursingUnit dimension and just count the Hospital responses where answer = 1. What I'm actually getting is
State Hospital Nursing Unit Hospital Top Responses Nursing Unit Top Responses
AL A 1 0 10
AL A 2 30 5
AL A 3 0 15
CA B 1 100 99
CA B 2 0 1
One and only one of the "Hospital Top Responses" returns its correct value. The rest of the rows associated with each hospital returns 0. I need every row to return their respective Hospital Top Response. The "Nursing Unit Top Responses" column is correct for all.
I hope that makes sense.
