8 Replies Latest reply on Sep 22, 2014 2:37 PM by philmodjunk

    Chart Based on Sub Summary Data

    sccardais

      Title

      Chart Based on Sub Summary Data

      Post

      I want to create a chart based on data in a Sub Summary report. 

      A screenshot (partial) of the Sub Summary report is attached. I want to chart the first column (Year / Mo) and the 6th column, Net Promoter Score.

      Year / Mo is a calculation field, indexed with Date result.

      Net Promoter Score is a calculation field with unstored number result based on Summary fields using GetSummary function.

      I would like to create this chart in a separate layout and I would like to be able to limit the chart to display data from different found sets using fields not on this layout. For example, a subset of Surveys from customers using one or more specific versions of the product which is stored in a field not shown on this layout.

      Is it possible to do this?

      How?

       

      NPS_Surveys_Sub_Summary.png

        • 1. Re: Chart Based on Sub Summary Data
          philmodjunk

          I would like to create this chart in a separate layout

          And can that layout be based on the same table or a different table? (This might work from a table where you have one record for each month-year value.)

          Net Promoter Score is a calculation field with unstored number result based on Summary fields using GetSummary function.

          Please post that calculation. Depending on table occurrence context and the exact details of this calculation, you may or may not be able to use this calculation as your "y series" data.

           

          • 2. Re: Chart Based on Sub Summary Data
            sccardais

            Here's the calculation:

            (GetSummary(s_zPromoter_RT;c_Survey_YYYY_Mo)/GetSummary(s_CountResponseID_RT;c_Survey_YYYY_Mo)

            -GetSummary(s_zDetractor_RT;c_Survey_YYYY_Mo)/GetSummary(s_CountResponseID_RT;c_Survey_YYYY_Mo))*100

            --

            If charting within FileMaker is not possible, can the data in the sub summary be exported to either Excel or csv file? I tried this but only the first row of data was exported.

            Thanks

            • 3. Re: Chart Based on Sub Summary Data
              philmodjunk

              I see no reason yet why you can't chart this data using FileMaker--though there are limitations that sometimes make the exact chart that you want impossible. I just need details in order to come up with something that works.

              Still waiting for an answer to this question:

              And can that layout be based on the same table or a different table?

               

              • 4. Re: Chart Based on Sub Summary Data
                sccardais

                Not sure I understand your question.

                I want the chart to be based on the data shown on the data shown in the screen shot so the chart layout would be based on the same tables used to create this layout.

                The other fields I mentioned are also in the same tables used to create the layout shown in the screenshot.

                Does this answer your question or am I missing it?

                 

                • 5. Re: Chart Based on Sub Summary Data
                  sccardais

                  By the way, based on another forum posting, I discovered how to export the sub summary data to an Excel file that I can use to create my charts.

                  I'd still prefer to do it inside FMP if possible though.

                  • 6. Re: Chart Based on Sub Summary Data
                    philmodjunk

                    You specified "a different layout", so I needed to know if that "different layout" meant that a change in context was also specified here or if your "different layout" can be based on the same table occurrence as that used in your screen shot posted at the start of this thread and thus can be based on the same found set. The issue here is that your calculation field computes a value based on the current found set so that change in "context" would affect our charting options.

                    This looks to me to be pretty simple to set up.

                    Specify your calculation field as your Y-Series data source.

                    Make sure that your chart's data source options are specified for "current found Set", "Summarized Groups of Records" and then sort your records by the Year - Month field to group your records by year and month.

                     

                    • 7. Re: Chart Based on Sub Summary Data
                      sccardais

                      Thanks. Works perfectly once I put the chart in a Body part (not Sub Summary) and changed to View as Form.

                      I have three additional questions / issues with this layout.

                      Find Using Checkbox: I would like to adjust the chart to view the data by the version of the product being used by the person who completed the Survey. This information is stored in a field "Version" in the same table the layout is based on.  Ideally, this would be done with a checkbox view of the Version field to allow multiple Find criteria. This seems like a pretty "normal" requirement. Is there a thread or discussion you know of that describes how to do this?

                      Display Find Criteria on Layout. I would also like to display the Find Criteria somewhere on the layout. 

                      Automatically Sort After Find. I want to sort the records by the date immediately after the Find. I have a script for this but for some reason, I cannot find a script trigger setting that will perform the Script. What might I be missing?

                       

                      • 8. Re: Chart Based on Sub Summary Data
                        philmodjunk

                        See the scripted find examples found here: Scripted Find Examples

                        Pay close attention to the looping script examples near the end.

                        By setting up a global field for the check box format, you not only have a way to perform the find, but also to show the criteria. A global field with a check box format could also be used with Go to Related Records to find the same records by setting up a self join relationship with the check box formatted field as the match feld--so that is yet another approach that might be used.

                        And your script that finds records can simply include a sort records step to follow the perform find. No script trigger is needed.