5 Replies Latest reply on Feb 12, 2009 10:07 AM by TSADesign

    Date Ranges



      Date Ranges


      I am trying to generate a monthly sales reports for one of my clients. The sales report consists of 4 tables, with Table 1 being the 2009 sales details for this team of 9 sales people. With each Find for a specific sales person by name, I have created a calculation that goes to Table 2 to pull that sales person's Monthly Average Sales for 2007, then a separate calculation that goes to Table 3 to pull that sales person's Monthly Average Sales for 2008, then a separate calculation that goes to Table 4 to pull that sales person's Monthly Goals for 2009. On Layout 1 management is able to see full details of who the Found salesperson sold to, what they sold, what their profit was, and how they compare in sales to the monthly averages of 2007 & 2008, as well as how they are doing against their goal for 2009. Then on Layout 2, I consolidate all of this into a single sheet summary of information.


      This works great when only looking at one month at a time, however as the year progresses I need to be able to accommodate viewing multiple months of data simultaneously. The challenge to this becomes how to determine how many months the query is based upon, (are they looking at January February March = 3 months, or January - June = 6 months, etc.) and then to calculate that number of months times the monthly averages of 2007, 2008, and the goal for 2009. 


      The report that I am getting this data from doesn't output a date for the report, however it is easy enough for me to input a date for each months worth of data. When importing the data into Table 1, I can simply Replace Field Contents and put a month's name in, ie. for January I put in January, for February I will put in February, etc. I don't have any ability to identify sales in Table 2 or 3 (2007 or 2008 data) because that data was generated at the end of each year, so I can only take the results and divide by 12 to get a monthly average to compare against. I am assuming I will need to accommodate a Begin Date (i.e. January) and an End Date (i.e. March), but how can I identify that I need to multiply all other data times 3 in order to track that salesperson's success against their sales?


      Any help would be greatly appreciated. 



      Joe Beaver
      TSA Design & Consultancy

        • 1. Re: Date Ranges

          Hi Joe


          Sorry no one has managed to come back to you with a solution for this. have you solved this yet or still need some help?


          Reading through your post I am trying to work out your table and data structure. If you could give an outline of tables and relationships that would help in visualising what you have currently.


          "The challenge to this becomes how to determine how many months the query is based upon"


          Is this a users search criteria on the sales report? Is this scripted or a manual search?


          And finally for now, are you trying to display monthly figures going back x number of month in a sort of column view or trying to work out a value based on the found set a user is in.


          Hopefully we can find a solution for you if you have not already got there.

          • 2. Re: Date Ranges



            Thank you for getting back to me. I have figured out a way to do this, that may not be the most seem-less approach, but it works. 


            My question can be narrowed down to - does FMP have the intelligence to determine how many months are between two fields. Meaning, if I have a field name Beg. Month that is populated by the value January, then a second field called End Month, that was populated with the value March, could FMP determine that this query represent 3 months of data? If it can generate that number (3), then I can use that to trigger the rest of my calculations.


            By the way, my work around requires the user to trigger how many months of data they are looking at through a drop down list of values 1-12.


            Thanks again for responding - I'm still curious if this can be done because it eliminates the user needing to input the number of months of data being culled.


            Thanks again, 

            • 3. Re: Date Ranges

              Yes, but you would need to convert the month to a number 1-12 and minus the End Month from the Beginning Month.


              a fairly standard calculation to work out the month number is


              Position ( "00JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC" ; Left ( Upper ( Month ) ; 3 ) ; 1 ; 1 ) / 3 

              If you have Advanced you could put this into a Custom Function otherwise your final calc shoudl look something like this:


              Let ( [
              Start = Position ( "00JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC" ; Left ( Upper ( StartMonth ) ; 3 ) ; 1 ; 1 ) / 3 ;
              End = Position ( "00JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC" ; Left ( Upper ( EndMonth ) ; 3 ) ; 1 ; 1 ) / 3 
              ] ;
              End - Start


              Let me know if this helps, and if you need any help setting up the Custom Function, if you have Advanced then let us know.

              • 4. Re: Date Ranges
                   That shouldn't be too difficult - try:

                Let ( [
                array = "janfebmaraprmayjunjulaugsepoctnovdec" ;
                start = Ceiling ( Position ( array ; Left ( BeginMonth ; 3 ) ; 1 ; 1 ) / 3 ) ;
                end = Ceiling ( Position ( array ; Left ( EndMonth ; 3 ) ; 1 ; 1 ) / 3 )
                ] ;
                Mod ( end - start ; 12 ) + 1

                BTW, reading your original description, it seems you have a separate table for each year's data. That is not a good idea, IMHO.

                • 5. Re: Date Ranges
                     Thank you for this... I can't wait to try it out!!