9 Replies Latest reply: Oct 20, 2015 2:24 PM by Christopher Woodford RSS

    Creating Average for Certain Columns and Rows

      I am making a dashboard with data that looks like the following

       

      ID, Name, Relationship, C1,  C1,  C2,  C2, C3, C3, C4, C4, C1,  C1,  C2,  C2, C3, C3,  C4,  C4, C1, C1, C2, C2, C3, C3, C4,  C4

      1     JJ     Crew               1     2     5     5     6     6     5     3

      2     JJ     Crew               3     3     2     6     2     2     3     4

      3     JJ     Self                                                                       2     2     3     4     5     6     1     1       

      4     JJ     Super                                                                                                                         4     4     5     6     5     6     6     2

      5     JJ     Super                                                                                                                         5     2     1     2     3     4     5     2  

      6     KK    Super                                                                                                                         3     2     3     6     1     2     5     4  

      7     KK     Self                                                                      3     4     2     1     5     3     2     4    

      8     KK     Crew              2     3     4     1     3     5     3     2


      What I am looking to do is display the average score of the selected Name for each "C" category so for example when I click "JJ" selected QV should display the average of all of JJs C1, C2, C3, C4 scores - So it would be like a bar graph that has a bar for the average total C1 score, another bar for the average total C2 score ect.


      Is this possible and can someone please help.


      Thank You!

        • Re: Creating Average for Certain Columns and Rows
          Crystle Stamper

          I'm trying to understand the data, so a quick question.

           

          Why is it that the ID, Name, Relationship fields have the C1, C2 etc fields in them.... that doesn't make sense to me?

            • Re: Creating Average for Certain Columns and Rows
              Crystle Stamper

              OK now i see. The last row was carried over.

               

              Well, the main problem I see is that you have a lot of the C1, C2 fields that are duplicates. Are these all the same four fields? If so, it would be best if they were combined into just the 4 fields. Then all you would need to do is load the table and it would work correctly.

                • Re: Creating Average for Certain Columns and Rows

                  Thank you for your response, yes the last row carried over.

                   

                  The C1, C2, C3, C4 are all survey questions that are worded differently to tailor to the audience. That is why when the relationship is supervisor there is a certain set of questions they answer, the crew answer another, and the self answer another that is why they are seperated like that.

                   

                  The numbers are not duplicates, each row is a response from a different individual evaluating JJ or KK. C1 are character questions, C2 communication, C3 competence and C4 connection and even though there are two C1, C2, C3, C4 all the questions are different but I want to get the average for each C

                   

                  Hope  You can Help

                    • Re: Creating Average for Certain Columns and Rows
                      Digvijay Singh

                      it might appear somewhat similar to below, let me know if any tweaking is required.

                      graph.PNG

                        • Re: Creating Average for Certain Columns and Rows

                          That is how I want it to look, but is there a way to do it without creating a crosstable?

                           

                          Also keep in mind that in this example the column names appear the same but in reality the columns are different more like C1.1, C1.2, C2.1, C2,2, C3.1, C3.2, C4.1, C4.2, C1.3, C1.4, C2.3, C2.4, C3.3, C3.4, C4.3, C4.4, C1.5, C1.6, C2.5 ect. and i want to find the average off all C1, C2, C3, C4

                            • Re: Creating Average for Certain Columns and Rows
                              Crystle Stamper

                              Maybe you could add a new field, or title. Have them labeled as both C1.1 and C1. The C1 field would be the main group, and the C1.1 would be the secondary definition of the group. That way you could select either or both.

                              • Re: Creating Average for Certain Columns and Rows
                                Digvijay Singh

                                Now it looks easier as we can have diff field names, now we can do it without cross table , check below -

                                model and draft design is ready but you need to check/verify/correct if avg func is applied as needed or not, need to verify the calculation.

                                avg.PNG

                                Script is like below -

                                Load ID, Name, Relationship,

                                  RangeAvg(C1.1,C1.2,C1.3,C1.4,C1.5,C1.6) as C1Avg,

                                  RangeAvg(C2.1,C2.2,C2.3,C2.4,C2.5,C2.6) as C2Avg,

                                  RangeAvg(C3.1,C3.2,C3.3,C3.4,C3.5,C3.6) as C3Avg,

                                  RangeAvg(C4.1,C4.2,C4.3,C4.4,C4.5,C4.6) as C4Avg;

                                Load * Inline [

                                ID, Name, Relationship, C1.1,  C1.2,  C2.1,  C2.2, C3.1, C3.2, C4.1, C4.2, C1.3,  C1.4,  C2.3,  C2.4, C3.3, C3.4,  C4.3,  C4.4, C1.5, C1.6, C2.5, C2.6, C3.5, C3.6, C4.5,  C4.6

                                1,     JJ,     Crew,    1,     2,   5,     5,   6,   6,   5,   3,

                                2,     JJ,    Crew,     3,     3,     2,     6,     2,     2,     3,     4,

                                3,     JJ,     Self,,,,,,,,,                               2,     2,     3,     4,     5,     6,     1,     1,      

                                4,     JJ,     Super,,,,,,,,,,,,,,,,,                                                           4,     4,     5,     6,     5 ,    6,     6,     2

                                5,     JJ,     Super,,,,,,,,,,,,,,,,,                                                          5,     2,     1,     2,     3,     4,     5,     2 

                                6,     KK,    Super,,,,,,,,,,,,,,,,,                                                          3,     2,     3,     6,     1,     2,     5,     4 

                                7,     KK,    Self, ,,,,,,,,                               3,     4,     2,     1,     5,     3,     2,     4   

                                8,     KK,     Crew,              2,     3,     4,     1,     3,     5,     3,     2

                                 

                                 

                                ];

                            • Re: Creating Average for Certain Columns and Rows
                              Crystle Stamper

                              Yes, if you just combine the C1, C2, C3, C4 fields, and carry over their corresponding fields for ID, Name, Relationship, then you will get a data model that you can create the tables and  charts like Digvijay Singh posted above.

                        • Re: Creating Average for Certain Columns and Rows

                          I still have not been able to get the data to work with me. Does anyone have any other ideas?