I need to produce final values that are based on variables I have defined as both dimensions and measures, and I'm running into #MULTIVALUE errors.
I have a set of test questions, the universe as defined provides a unique test instance (dimension) question code (dimension), and response (dimension) and a response count (measure).
In my query I have defined several variables.
Question Section (dimension) - if/else formula that assigns a section name based on the question code.
A raw score (dimension), which converts likert scale responses into numeric values
Question Mean Score (measure) sum(raw score) / response count
So far so good.
Now I want to average my mean scores by Question Section.
Example: Question Section Alpha consists of question codes A1, A2, and A3. I have Question Mean Scores of 88.4, 96.1, and 79.4 respectively.
If I create another variable Section Mean Score (measure) in a variety of ways, I'm getting #MULTIVALUE errors. My current definition for Section Mean Score is an if/else formula using Question Section. If Question Section = Alpha then sum(Question Mean Scores)/3.
I'm fairly certain the problem is that since more than question code belongs to a question section, it's trying to perform the Section Mean Score multiple times (once for each member of the section), but I'm not sure how to get past that.
I've more experience in Cognos, where my variables would be calculated fields on two queries (say question section and raw score on one, the measures on the other), and then the joined query could have a calculated field of it's own performing the aggregate math.
Combined queries don't immediately appear to fit my need here. Any suggestions for tools I'm overlooking?