Thank you very much for your help and advice. I want to make sure I have understood your suggestion properly.
Are you suggesting that I should load a separate table that will have each Survey/Question combination as a row ?
Answer 1 Q1 ABC
Answer 1 Q2 Correct
Answer 1 Q3 A
Answer 2 Q1 ABC
As I have a number of dashboards preset using all the question as dimensions, I would need to set up this new cross table completely independently for the rest of my data set.
Have I understood this correctly? Thanks Helene
Dear Jagan and Vineeth
Thank you very much for both your suggestions. I now have created a cross table and my data looks like this:
SurveyAnswer | Question | Answer | CorrectYorN
Answer 1 | Q1 | ABC | Y
Answer 1 | Q2 | Correct | Y
Answer 1 | Q3 | A | N
Answer 2 | Q1 | ABC | Y
Answer 2 | Q2 |Correct | Y
Answer 2 | Q3 | A | N
Answer 7 | Q1 | DEF | N
Answer 7 | Q2 | Correct | Y
Answer 8 | Q1 | ABC | Y
Answer 8 | Q2 | Correct | Y
Answer 8 | Q3 | C | Y
In my previous example, the number, the number i was after is the average of those 3 %
- Q1 % Correct = 6/10 = 60%
- Q2 % Correct = 6/8 = 75%
- Q3 % Correct = 4/7 = 57.14%
So (60+75+57)/3 which is not the same as (6+6+4)/(10+8+7).
I can using the set restrictions to calculate each individual % but I am still unclear how to get a total average as per my definition above using a formula than would span the whole cross table.
Don't I need to still need to store each individual % Correct for Q1, Q2, and Q3 and then average them in a two step process ? Maybe i am confused.