7 Replies Latest reply on Sep 13, 2013 9:25 AM by philmodjunk

    Compare dates for same week of current year to prior year totals

    JohnPiscopo

      Title

      Compare dates for same week of current year to prior year totals

      Post

           I am new to FM Pro.  I have a database that has one field for the date of service. I need to create a report that will compare the totals for the month of the current yr to the prior year and list the count difference and the percent of the difference.

           Ex jan 2012  Total 285   Jan 2013 Total 302   17 Difference  6 % increase

           I also need to do the same calculation on a weekly report comparing the same date (which is always a Sunday) for the prior yr vs the current year.

           Ex. 01/08/2012  Total 291  01/06/2013 Total 310   19 Difference  7 % increase

           I also need to do the same calculation on a weekly report comparing the same date (which is always a Sunday) for the prior week  vs the current week.

           Ex. 01/06/2013  Total 310  01/13/2013 Total 303   7 Difference  2 % decrease

           In a decreased value I would want to show this in Red.

           I appreciate any help you could provide. Thank you.

        • 1. Re: Compare dates for same week of current year to prior year totals
          philmodjunk

               And how is your data structured in the table from which you want to make these comparisons? Is that "total" a value in a single record or a group of records?

               And what makes 1/8/2012 and 1/6/2013 the two weeks to compare? The fact that they are both the 2nd week in January? Or that they are the 2nd week of the year? (not always the same, I think...)

          • 2. Re: Compare dates for same week of current year to prior year totals
            JohnPiscopo

                 The total is one field in the DB and has an attendance value for each date,

                 01/06/2013 may have 285 total   01/13/2013  310

                 01/08/2012  may have 224 total  01/16/2012   234   and so on.

                 The dates being compared will always be Sunday. The Sunday date of the current year will always be compared to the Sunday date of the prior year that is closest the to the current years Date. From time to time the closest date may be in a different month.

                 Thank you

            • 3. Re: Compare dates for same week of current year to prior year totals
              philmodjunk

                   I know that it's a Sunday, but that doesn't answer my question.

                   1/8/2012 is the second Sunday of 2012. 1/6/2013 is the first Sunday of 2013.

                   What are your "rules" that match these two Sundays? You could be specifying that these match because they match the 2nd week of 2012 to the second week of 2013 and that the week of 12/30/2013 should match to the week of 12/1/2012 as these are the first weeks where some of the days in that week are from the first week of each year. Or your example could be a typo. I'm just trying to clarify the rules before suggesting a method to produce the results that you want.

              • 4. Re: Compare dates for same week of current year to prior year totals
                JohnPiscopo

                     My Apologies,  it was a typo,  The dates should match the same weeks of the month, The first week should match the first week of both years, second week matches second week both years and so on. Thank you

                • 5. Re: Compare dates for same week of current year to prior year totals
                  philmodjunk

                       Sorry, but I'm just not getting across the question that I'm trying to ask. Nearly any given year starts out with a partial week. If the week starts in Year 1 and ends in Year 2, do you want to treat that as a week in Year 1 or a week in Year 2?

                       The difference is in whether or not we look at the Year value for the first day of the week, the last day of the week or possibly the Friday of that week when setting up a relationship to match the correct pair of records for your week based comparison.

                  • 6. Re: Compare dates for same week of current year to prior year totals
                    JohnPiscopo

                         If the week starts in year 1 and ends in year 2

                         I want to treat year 2 as being in year 1.

                         Sorry for not being clear on my part.

                         Thank you for your help. 

                    • 7. Re: Compare dates for same week of current year to prior year totals
                      philmodjunk

                           Warning: You are probably not going to understand all of this in one go. You'll need to look stuff up in any training resources you have and FileMaker Help and then ask follow up questions. This is a lot to take in all at once.

                           To restate, If Sunday falls in 2012 and Friday of the same week in 2013, the entire week is treated as the last week in 2012, not the first week in 2013. There's a function we can take advantage of called "week of year", but it would return a 1 for the dates. 1/1/2013 through 1/5/2013.  WeekOfYear ( 1/6/2013 ) returns a 2 so we have to take that difference into account with any calculations we set up to match a record to the "same week of the previous year".

                           I am also assuming that you have one record for each week of the year and that to compare months, you need to compare a combined or "summary" total for the month to the same month of the preceding year. Let me know if this is not how you've set this up.

                           What you are dealing with here is what I call a "selective sum". You need totals from your table but just for a selected subset of all the records in your table. You can do this with a filtered portal that only shows records that meet the filter criteria or with a relationship that matches only to the desired set of records. The relationship can be set up in Manage | Database | Relationship or it can be part of a SQL expression used with the ExecuteSQL function that is a new feature of FileMaker 12.

                           Here are some useful calculations for using with any of these methods:

                           A common value for all records from the same month:

                           YourDateFIeld - Day ( YourDateField ) + 1 // select "Date" as your return type.

                           This computes to the date of the first day of that month.

                           Date for 1st of month, same month of previous year:

                           Let ( m = YourDateFIeld - Day ( YourDateField ) + 1 ; Date ( Month ( m ) ; 1  ; Year ( m ) - 1 ) )

                           Adjusted Week/year field:

                           Let ( [ D = YourDateField ;
                                      Y = Year ( D ) ;
                                      Wy = WeekOfYear ( D ) - ( DayOfWeek ( Date ( 1 ; 1 ; Y ) ) > 1)
                                   ];
                                     Y & Right ( "0" & Wy ; 2 )
                                   ) // let

                           Same Week, previous year:

                           Let ( [ D = YourDateField ;
                                      Y = Year ( D ) ;
                                      Wy = WeekOfYear ( D ) - ( DayOfWeek ( Date ( 1 ; 1 ; Y ) ) > 1)
                                   ];
                                     Y - 1 & Right ( "0" & Wy ; 2 )
                                   ) // let

                           You can set up calculation fields with these expressions and use them as match fields in relationships or you can use them in portal filters or even to compute parameter data for the ExecuteSQL function call.

                           And for either filtered portals or a relationships that match with these calculations in calculation fields, you can set up additional Tutorial: What are Table Occurrences? of your existing table in order to link the table to itself in a relationship. (We call that a "self join".)