5 Replies Latest reply on Apr 1, 2014 2:11 PM by philmodjunk

    Chart: Plot Summary Data Over Time

    sccardais

      Title

      Chart: Plot Summary Data Over Time

      Post

           I'm having trouble plotting Summary data on a standard column chart.

           All of the data needed is in one table: Surveys.

           X Axis = Year_Month

           The x axis is based on a calculation field (Year_Month) that converts a date in another field to yyyy-mm format. This allows grouping of Surveys by month.

      Y Axis = Net Promoter Score

           Surveys have a field "Score" containing a number value between 1 - 10.

           A calculation field "NPS_Category" assigns each Survey to a group depending on the value in "Score."

           NPS_Category can hold one of three values, "Promoter", "Passive" or "Detractor".

           The Net Promoter Score is calculated by subtracting the % of records in the found set classified as "Detractors" from the % classified as "Promoters.".

      EXAMPLE

           For 100 Surveys with Year_Month = 2014-03.

           40 are classified as Promoters. 20 as Detractors.

           The Net Promoter Score for 2014-03 would be 20%. (40/100)-(20/100)*100

           I'm having trouble getting the y axis to adjust for each change in Year_Month. It's showing the same value rather than adjusting up and down.

            

            

            

        • 1. Re: Chart: Plot Summary Data Over Time
          philmodjunk

               Is the y-Axis charting a single value from your table or a summary value computed from a group of records in your layout's found set? (not from a related table)

               In otherwords, are you trying to chart a total, average or maximum score for all surveys for a given month-year? And you have multiple records in your layout's found set for each month year?

               Or is this a single value--either from a single record or a calculated value that is the same for all records with the same month-year?

          • 2. Re: Chart: Plot Summary Data Over Time
            sccardais

                 I want to chart sub summary data from all records when sorted by Year_Month. There are multiple records in each Year_Month group.

                 I want the Y Axis to plot summary data from a group of records in layout's found set - not related. The result will be Net Promoter Score.

                 Net Promoter Score is calculated based on other summary fields in the same table - not related.

                 Net Promoter Score = (Count all Surveys classified as Promoters / Found Count) - (Count all Surveys classified as Detractors / FoundCount)

                 The EXAMPLE in the first post of this thread explains it in more detail.

                 Does this answer your question?

                 Thanks

            • 3. Re: Chart: Plot Summary Data Over Time
              philmodjunk

                   Does that mean that if you created a summary report on this layout, each bar in your graph would be for a value shown in a sub summary layout part of that report?

                   If so, you can specify that the Y-Axis value be your summary field as long as you select the "summarized data" option in the data source section of Chart Setup.

              • 4. Re: Chart: Plot Summary Data Over Time
                sccardais

                      The short answer is that I am not able to calculate the Net Promoter Score as I want.

                     The screenshot shows the layout I'm starting with. Ultimately, I want a chart but I'm starting with a sub summary report on the assumption that it's a good starting point.

                     The sub summary part is sorted on Year_Month.

                     The field labeled Net Promoter Score is supposed to calculate the Net Promoter Score for each group of Year_Month. It doesn't. After sorting on Year_Month, Net Promoter Score in the sub summary part is blank.

                     Here's the formula that's not working:

                     (GetSummary(Summary zPromoter;Year_Month)/GetSummary(Count_Surveys;Year_Month)
                     -GetSummary(Summary zDetractor;Year_Month)/GetSummary(Count_Surveys;Year_Month))*100
                      
                     Summary zPromoter is a Total of a calculation field with a 1 if the NPS Category is Promoter.
                     Summary zDetractor is a Total of a calculation field with a 1 if the NPS Category is Detractor
                      
                • 5. Re: Chart: Plot Summary Data Over Time
                  philmodjunk

                       The records present in your found set and the specific sort order that you have used to sort them will affect what value is returned by your getsummary functions.

                       I would break this down into it's component parts and see if each use of GetSummary is or is not computing the expected value.