12 Replies Latest reply on Mar 7, 2014 1:32 PM by philmodjunk

    Sub Summary Based on Date Calculation

    sccardais

      Title

      Sub Summary Based on Date Calculation

      Post

           I want to create a sub summary report based on a date calculation.

           The attached layout has two sub summary parts.

           The first summarized by Edition.

           The second by the calculation field Survey_YYYY_Mo. This field is based on another calculation field (Date_Convert) that converts a timestamp to a conventional date. Survey_YYYY_Mo converts Date_Convert to only the year and month of the survey because I want them grouped by Year and Month to track trends.

           The goal is to identify trends for each version of the product over time based on the year / mo the surveys were taken. e.g. if three surveys were submitted in Feb 2014, their results would be summarized into a single row 2014 - 02.

           I'm sorting by Edition and Survey_YYYY_Mo but instead of getting summary data below each Edition, I'm getting one row with a question mark ? in the Survey_YYYY_Mo field. I would expect each version to have multiple rows - one row for each group of surveys by year - mo they were taken.

           Can anyone tell me what I'm doing wrong here?

      NPS_by_edition_over_time.jpg

        • 1. Re: Sub Summary Based on Date Calculation
          philmodjunk

               The ? is FileMaker's way of telling you that it cannot display a value in that field. This might happen if the field is too narrow to show the value, but it can also indicate a calculation error such as dividing by zero in the field's calculation. I suspect a calculation error given the fact that you aren't getting multiple rows of sub summarized data here.

               Better check your calculation field and see what values it is returning. Also check the result type, a result type of text should be used for the example result you show in your post, not a number result type.

          • 2. Re: Sub Summary Based on Date Calculation
            sccardais

                 Thanks. That helped.

                 Changing calc result to text got rid of the question mark but it also prevents sorting by date in ascending or descending order.

                 Is there a way to sort the results by date? 

                  

            • 3. Re: Sub Summary Based on Date Calculation
              philmodjunk

                   Text in this format: 2014 - 02

                   should sort correctly in ascending an descending order.

                   I do use a different way to get "Month-year" values for such sorting/grouping, but the above results should work as the number of digits in each value should be the same due to the leading 0 for single digit month numbers.

                   My preferred method is to compute a date for the first day of the same month, keeping my result type as "Date":

                   DateFIeld - Day ( DateField ) + 1

                   I can use custom date formatting to show the same Year - Month format you have or any number of other custom date formats and since these are date fields, they will sort in the correct order when I sort on them.

              • 4. Re: Sub Summary Based on Date Calculation
                sccardais

                     I'm getting 2014 - 2 with this formula for Survey_YYYY_Mo 

                       
                •           Year ( Date_Convert )&"-"&Month(Date_Convert) Result is Text
                •      
                •           Date_Convert is calculation field with this formula: GetAsDate(Timestamp) Result is Text

                     I don't see a way for format the month in Survey_YYYY_Mo with leading zeros.

                     ??

                • 5. Re: Sub Summary Based on Date Calculation
                  philmodjunk

                       & RIght ( "0" & Month(Date_Convert ) ; 2 )

                  • 6. Re: Sub Summary Based on Date Calculation
                    sccardais

                         Thanks.

                         The text manipulation you suggested above worked perfectly.

                         New formula for Survey_YYYY_Mo is Year ( Date_Convert )&"-"&Right("0" & Month(Date_Convert);2)

                    • 7. Re: Sub Summary Based on Date Calculation
                      sccardais

                           The changes to the Date_Convert and Survey_YYYY_Mo fields now summarize results by the month but the calculation for Net Promotor Scores are showing the same value for all dates rather than recalculating for each group of records by year / month.

                           The field for Net Promoter Score is calc field ( c_NPSCalc) in Surveys table. 

                           Formula for c_NPSCalc is

                           (GetSummary(Summary zPromoter;JR Edition)/GetSummary(Count_Surveys;JR Edition)

                           - GetSummary(Summary zDetractor;JR Edition)
                            
                           /GetSummary(Count_Surveys;JR Edition))*100
                            
                           The overall Net Promoter score for Product A in screenshot attached is correct (11.51) but the individual rows are not. For example, the Net Promoter Score for 2013-09 should be 26. ((10/27)- (3/27)) * 100
                            
                      • 8. Re: Sub Summary Based on Date Calculation
                        philmodjunk

                             And is this layout based on the Surveys table or some other table?

                             Is each row shown here a sub summary layout part produced row or a record from the body of your layout?

                        • 9. Re: Sub Summary Based on Date Calculation
                          sccardais

                               The layout is based on the Surveys table.

                               Each row is produced by the sub summary part. There is no body part.

                               Thanks

                          • 10. Re: Sub Summary Based on Date Calculation
                            philmodjunk

                                 But I would guess that you can't sort the records for this report by the JR_Edition field and get the correct result since it would apear that you need to sort to group your records by year - month.

                                 Thus, you need a new calculation that uses the same functions, but specifies a different break field.

                            • 11. Re: Sub Summary Based on Date Calculation
                              sccardais

                                   Can you elaborate on this? I don't understand.

                              Thus, you need a new calculation that uses the same functions, but specifies a different break field.

                              I'm currently sorting on Edition and Survey_YYYY_Mo

                                    

                              • 12. Re: Sub Summary Based on Date Calculation
                                philmodjunk

                                     To repeat what I posted in the thread where we first discussed this: "In order to get the same sub total from a summary field as you would get if you placed that field in a sub summary layout part, you must specify the same "break field" parameter as the "when sorted by" field specified for that sub summary layout part."

                                     (GetSummary(Summary zPromoter;Survey_YYYY_Mo )/GetSummary(Count_Surveys;Survey_YYYY_Mo )

                                     - GetSummary(Summary zDetractor; Survey_YYYY_Mo )
                                      
                                     /GetSummary(Count_Surveys; Survey_YYYY_Mo ))*100