9 Replies Latest reply on Aug 29, 2012 1:56 PM by dchabot

    Need to determine the 12th of the month

    dchabot

      Hi,

       

      I need to indicate whether or not an employee worked on the 12th day of each month during a reporting period.

      The reporting period would be for each quarter.

       

      This is the exact information needed:

       

      12th of the month - month 1

      Indicate whether or not the employee worked on the 12th day of each month during the reporting period.

      Enter "1" if the employee worked during the week in the third month of the quarter that contained the 12th of

      the month. Enter zero if the employee did not work during the period.

       

      12th of the month - month 2 - same as above

      12th of the month - month 3 - same as above

       

       

      The pay date field may not necessarily be exactly the 12th day of the month.

       

      For example:

      1/6/2012

      1/13/2012 = "1"

      1/20/2012

      1/27/2012

       

      2/3/2012

      2/10/2012

      2/17/2012 = "1"

      2/24/201

       

      3/2/2012

      3/9/2012

      3/16/2012 - "1"

      3/23/2012

      3/30/2012

       

      Let M1 = "week of the month that has the 12th in it"

      Let M2 = "week of the month that has the 12th in it"

      Let M3 = "week of the month that has the 12th in it"

       

      It's the 3rd week (5 days) of each month range: Month(Week of Month=3 ???

       

      What is the formula to give the week of the month that has the 12th in it?

       

      This could then be:

       

      Case(PayPeriod=M1;1

      PayPeriod=M2;1

      PayPeriod=M3;1;0)

       

      Excuse any of my thought processes above, as I'm starting to ramble on.......

      This is possible to do in FM isn't it?

       

      Thanks so much

       

      DChabot

      FM11 Adv

        • 1. Re: Need to determine the 12th of the month
          comment

          I got a bit confused, esp regarding what information is available to begin with. If you have a date field, and you want to find out if the date is in the same week* that contains the 12th of the month, try =

           

          WeekOfYear ( YourDate ) =  WeekOfYear ( Date ( Month (YourDate ) ; 12 ; Year ( YourDate ) ) )
          

           

           

          ---

          (*) A "week" in this context means Sunday to Saturday. inclusive.

           

          Message was edited by: Michael Horak

          • 2. Re: Need to determine the 12th of the month
            dchabot

            Michael,

             

            You nailed it!!!

            This fills in the correct '1' for every week of payperiod that includes the 12th of month for a year.

            Work perfect on the detail report.

             

            Here's the frustrating part.

            How do I get this detail over to a summary of this data for a quarter?

            I need to show a '1' not a summary of all the one's.

            • 3. Re: Need to determine the 12th of the month
              comment

              If this is for display only, you can format the summary field to display as Boolean.

              • 4. Re: Need to determine the 12th of the month
                dchabot

                Great idea but it's being exported to Excel.

                • 5. Re: Need to determine the 12th of the month
                  dchabot

                  Even as boolean it doesn't carry over into the Summary.

                     

                  • 6. Re: Need to determine the 12th of the month
                    comment

                    dchabot wrote:

                     

                    Great idea but it's being exported to Excel.

                     

                    Then how about changing the summary field to show the maximum of the calculation field? If, for some reason, you do need the total, you will need to add a calculation field, e.g. =

                     

                    GetAsBoolean ( GetSummary ( sTotal ; Quarter ) )

                    • 7. Re: Need to determine the 12th of the month
                      dchabot

                      Michael, maybe a picture will help to explain what the calc needs to be.

                       

                      This is the detail:

                       

                      DetailSUTA.jpg

                       

                      This is the Summary:

                       

                      SumSUTA.jpg

                       

                      If I can't get this to work, it can be done manually, but trying to avoid that route.

                      • 8. Re: Need to determine the 12th of the month
                        comment

                        I am afraid that doesn't tell me much. How exactly did you get "a summary of all the one's" you mentioned earlier? I thought you had a sub-summary part by quarter, but it doesn't look like you do.

                        • 9. Re: Need to determine the 12th of the month
                          dchabot

                          Michael, I finally got it working.

                          Using your original formula, I incorporated the Quarter and Month 1,2 or 3 of the quarter and then created summary fields.

                          It works perfect.

                           

                          New working formula:

                           

                          for Quarter:

                           

                          QTR_payperiod

                           

                          If(GetAsNumber(QTR Month_payperiod) = 1 or GetAsNumber(QTR Month_payperiod) = 2 or GetAsNumber(QTR Month_payperiod) = 3 ; 1; If(GetAsNumber(QTR Month_payperiod) = 4 or GetAsNumber(QTR Month_payperiod) = 5 or GetAsNumber(QTR Month_payperiod) = 6 ; 2; If(GetAsNumber(QTR Month_payperiod) = 7 or GetAsNumber(QTR Month_payperiod) = 8 or GetAsNumber(QTR Month_payperiod) = 9 ; 3; If(GetAsNumber(QTR Month_payperiod) = 10 or GetAsNumber(QTR Month_payperiod) = 11 or GetAsNumber(QTR Month_payperiod) = 12 ; 4 ;""))))

                           

                          for Month:

                           

                          QTR_Month_payperiod

                          Month(For Pay Period)

                           

                          Then these calcs are used in the report:

                           

                          Q12th of the month 1:

                           

                          If ( QTR Month_payperiod=1 ; WeekOfYear ( For Pay Period ) =  WeekOfYear ( Date ( Month (For Pay Period ) ; 12 ; Year ( For Pay Period ) ) ) ; 0)

                           

                          Q12th of the month 2:

                           

                          If ( QTR Month_payperiod=2 ; WeekOfYear ( For Pay Period ) =  WeekOfYear ( Date ( Month (For Pay Period ) ; 12 ; Year ( For Pay Period ) ) ) ; 0)

                           

                          Q12th of the month 3:

                           

                          If ( QTR Month_payperiod=3 ; WeekOfYear ( For Pay Period ) =  WeekOfYear ( Date ( Month (For Pay Period ) ; 12 ; Year ( For Pay Period ) ) ) ; 0)

                           

                           

                          Then I added summary fields for each of the Q12th of the months for the summary report.

                           

                          This is one way, and there is probably a less verbose way, but hey it works!

                           

                          Thank you so MUCH!

                          1 of 1 people found this helpful