

Re: Aggregating (Averaging) multiple dimensions and removing 0/Null values from the average
Helene Rao Feb 21, 2017 3:42 AM (in response to Vineeth Pujari)Dear Vineeth
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
etc
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

Re: Aggregating (Averaging) multiple dimensions and removing 0/Null values from the average
Vineeth Pujari Feb 21, 2017 4:20 AM (in response to Helene Rao)Sorry, I do not know how your existing data model is but; CrossTabbing the data will definitely simplify your calculations
Instead of adding individual questions as dimension you can simply drag the Question column and restrict using set analysis or calculated dimensions



Re: Aggregating (Averaging) multiple dimensions and removing 0/Null values from the average
jagan mohan rao appala Feb 21, 2017 4:06 AM (in response to Helene Rao)Hi,
Use CrossTable() to transform the data and validate the answer and create a flag whether it is correct or not then use this flag in set analysis to get the results.
Regards,
jagan.

Re: Aggregating (Averaging) multiple dimensions and removing 0/Null values from the average
Helene Rao Feb 23, 2017 3:44 AM (in response to Helene Rao)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
.. etc
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.
Thanks H