5 Replies Latest reply on Oct 23, 2009 3:19 PM by user14360

    Calculation for defining a date



      Calculation for defining a date


      I'm going insane trying to get this calculation to work.  Would love some help.


      I'm tracking weekly sales for various companies.  The year is divided into 4 quarters.


      There is a Company Records table with a primary key and a WeeklySales table matching up to the company records key.  There is also a global year and a global quarter field in the company records table.  In the weekly sales table there is a year field, quarter field, and a "week of quarter" field (ie, the numbers 1-13 depending on the week in the quarter).  The fields that match from the two tables are the primary key for the company, the year and the quarter fields.


      On the main Company Records layout, the user selects the year for the global year field from a drop down list, then a quarter for the global quarter field from the drop down list, then enters the sales data into a portal to the weekly sales data with one field for each 13 weeks of the quarter.  


      This then creates a record in the WeeklySales table with the year selected, the quarter selected, and the week number the data was entered into.


      What I need is for there to be another "date" field in the weekly sales table which enters the actual date for this particular record based on the year, the quarter and the week of the quarter.


      A complication is that for this scenario, Q1 actually is the final quarter of a year.  So Q2 is the first week of the year (starting on a Sunday), Q3 starts on the 14th week of the year, Q4 on the 27th week of the year and Q1 on the 40th week of the year.


      So far I've tried creating an "ActualWeekofYear" field, which tries to calculate the number of the week of the year (so Q2, Week 1 = 1 but Q3 Week 1 = 14), and then I would get another calculation to work out the date from that.


      But I can't get that first one to work yet. 


      These are the calculations I've tried so far, and so far I'm just getting  zero entered into the "ActualWeekofYear" field.



      Case (QuarterNEWSTRUCTURE = "Q2" ; WeekOfYear = WeekOfQuarter + 0;
      QuarterNEWSTRUCTURE = "Q3" ; WeekOfYear = WeekOfQuarter + 13;
      QuarterNEWSTRUCTURE = "Q4" ; WeekOfYear = WeekOfQuarter + 26;
      QuarterNEWSTRUCTURE = "Q1" ; WeekOfYear = WeekOfQuarter + 39)
      I also tried:
      Let ( [var1 = WeekOfQuarter; var2 = WeekOfQuarter + 13; var3 = WeekOfQuarter + 26; var4 = WeekOfQuarter + 39] ; 
      Case ( QuarterNEWSTRUCTURE = "Q2" ; WeekOfYear = var1;
      QuarterNEWSTRUCTURE = "Q3" ; WeekOfYear = var2;
      QuarterNEWSTRUCTURE = "Q4" ; WeekOfYear = var3;

      QuarterNEWSTRUCTURE = "Q1" ; WeekOfYear = var4 )) 



      Can anyone see where I'm going wrong?  Am I over-complicating it?


      Thanks for any help. 

        • 1. Re: Calculation for defining a date
             Sorry, I'm on Filemaker Pro 10 on a Mac, relatively new to Filemaker.
          • 2. Re: Calculation for defining a date

            Oh my goodness.  After leaving the problem and working on a different database, I realised where I was going wrong in my calculation.  I shouldn't be adding the "WeekofYear = " into the result area of each case statement.  I took it out and it's now bring back the correct week of year.




            Now I just need to work out how to get the week of year to bring back a date, referencing the "year" field.


            I assume that won't be too tricky. 

            • 3. Re: Calculation for defining a date

              For future reference, if you spot a major "oops" in a post here, you have the ability to edit the original post to correct it. Just select the Edit Message from the Options menu located just above the Kudos control on the right.


              Using your "ActualWeekofYear" calculated field,


              Date ( 1, ActualWeekofYear * 7-6, YourTable::YourGlobalYearField)


              Will compute a date for the Sunday of that week.


              Don't forget to change the calculation's result type to "Date" in place of the default "number" setting.

              • 4. Re: Calculation for defining a date
                   Not sure if it will fit into what you are looking to do, but you can also take a look at the WeekOfYearFiscal () function.
                • 5. Re: Calculation for defining a date

                  Thanks for both of your responses.


                  I'll give that calculation a go.  


                  I had a play with the WeekOfYearFiscal function, but thought it only worked the other way around, as in, getting the number of the week of that fiscal year from a date that is entered, rather than getting the date from the week number entered.


                  Am I wrong in that?  Can you also do it the other way around?