1 2 Previous Next 22 Replies Latest reply on Jan 18, 2012 9:45 PM by mikedorn

    summing a field for multiple years from a range of dates

    mikedorn

      Title

      summing a field for multiple years from a range of dates

      Post

      Employee table - Stores employee data

      Attendance table - A portal in the employee layout

      Review table - Stores employee yearly reviews

      All related by Employee_Id

      I need to summarize the Tardy_Count field in the Attendance table, over a date range to show on the yearly employee review. The date range is a sliding window of time, so that there are different yearly amounts for each employee.

      Examples: Date_Review is an entered date (4/11/2011) on the employees review report, Date_Start = Date_Review - 365 (4/11/2010). These dates will establish perimeters for the summary.

      I've got the summary working for the first year of the reviews but it doesn't work for any years after.

        • 1. Re: summing a field for multiple years from a range of dates
          Sorbsbuster

          How did you get it to work for one year?

          I assume you have:
          - a field marking the record as 'late'
          - a field set as a calculation, like StartDate = ReviewDate - 365

          You could set up a relationship to the attendance table where:
          - EmployeeID = EmployeeID
          - StartDate <= AttendanceDate
          - EndDate >= AttendanceDate

          then set a calculation field Counting the 'Late' marker via that relationship.

          • 2. Re: summing a field for multiple years from a range of dates
            mikedorn

            Shows examples of the marker you refer to

             

            In the Attendance table

            Tardy_Count_by_Date Calculation (Number) = Unstored from Attendance, = If (Date_Code >= Reviews::Date_Start and Date_Code <= Reviews::Date_End_Review) ; If (Code = "t" ; 1 ; 0 )) 

             

            In the Employees table

            Sum_Tardy_Count_by_Date Calculaton (Number) = Unstored, from Employees, - Sum ( Attendance_Self_Join::Tardy_Count_by_Date ), Evaluate even if all referenced fields are empty

            These formulas give me the required information for the first record of the Attendance table for the employee being reviewed.

             

            In the Reviews table

            A Layout uses (Sum_Tardy_Count_by_Date ) to show the number of Tardies for the given period of time within the range of dates for the employee review.

             

            How do I get it to evaluate all the records in the Review table for all employees?

             

            • 3. Re: summing a field for multiple years from a range of dates
              Sorbsbuster

              I think you could change the syntax of the first calculation to be a little simpler:

              Tardy_Count_by_Date Calculation (Number) = Unstored from Attendance, =
              If (Date_Code >= Reviews::Date_Start and Date_Code <= Reviews::Date_End_Review and Code = "t" ; 1 ; 0 )

              This calculation is dependent upon Reviews::Date_Start and Reviews::Date_End_Review  both being global fields.  (Did you intentionally change your naming convention?)

              I think you can either ignore the calculation in the Employees table, or ignore it in the Reviews table.  You question asked for it to display in the Reviews (although I think it would read better in the Employees Table).  You need a relationship from the Reviews to Attendance Table using EmployeeID = EmployeeID.  Then (having set the two global dates) this calculation should work:

              Sum ( Review_AttendanceByEmployeeID::Tardy_Count_by_Date )

              To show it on each Employee record you can set up a similar EmployeeID = EmployeeID relationship from the Employee Table to the Attendance Table, and a similar calculation will work.

              Sum ( Employee_AttendanceByEmployeeID::Tardy_Count_by_Date )

              I think it reads slightly better from the Employees Table because you can display the two global dates there and show the tardy count for those dates.  Changing records shows the next employees' count, etc.  Doing the same thing in the Reviews Table will give you mathematically the same result, but clicking to the next record will (potentially) display a review for the previous year, but still show exactly the same figures.  Then it's only when you click through a few more, and the Review is for another employee, that the figures jump to new values.  It is a small point, but would seem to read counter-intuitively.

              • 4. Re: summing a field for multiple years from a range of dates
                mikedorn

                I've been ill the last few days and not feeling like doing anything, brain not able to concentrate.

                 

                Something is wrong with my DB. I cannot get the above suggestion to work. I am filtering the Attendance portal by a calculation Global Data::g_Filter_Year = Year (Attendance::Date_Code). Will this affect the above suggestion/s?

                 

                • 5. Re: summing a field for multiple years from a range of dates
                  Sorbsbuster

                  Calculations work independently from a portal.  If you place a calculation (based on a a relationship) outside a portal based on the same relationship, then the calculation will use the relationship and not be affected by any filter applied to the portal.

                  Although in this case I don't see that it would affect the calculations described.

                  - In your Attendenace Table does each Attendance Record correctly show 0 or 1 when you change the global dates?

                  - If you place a portal of the Attendance Table (linked by EmployeeID ) in the Employee's Record, and show the list of attendance records including the 0 or 1, do they display correctly?

                  • 6. Re: summing a field for multiple years from a range of dates
                    mikedorn

                    This is my first try at a complex database. So far, the displayed data is showing what I expected. I think my calculations and relationships are working properly.

                    What would help clearify my exact probelm? Copy of ERD, Tables, and Fields?

                    • 7. Re: summing a field for multiple years from a range of dates
                      philmodjunk

                      Making and uploading a screen capture of Manage | Database | Relationships (sized and cropped to remove empty space) would be a start.

                      You can use the Upload an image controls just below the Post A Answer box for this. Just make sure to upload a Jpg, Gif or PNG file. The controls will appear to work with other formats such as BMP or PDF, but noting will show with your post.

                      • 8. Re: summing a field for multiple years from a range of dates
                        mikedorn

                        This is the most recent ERD. Some of the relationships may not be used any longer because of past tries at solving the above problem.

                         

                        I hope this makes sense. If not, let me know.

                         

                        thanks, 

                        • 9. Re: summing a field for multiple years from a range of dates
                          mikedorn

                          The calculations used to do the tracking do so by decoding and then counting the number of occurrences of a specific code entered into the attendance table via a portal on the employee record form. These formula are correctly keeping track of each specific code and displaying it in the attendance portal. Summary fields in the Employee table do the yearly data display.

                          The calculations may not be clean code but they do work.

                          Some of the fields may not be needed because of trial and error to get the correct formulation. 

                          • 10. Re: summing a field for multiple years from a range of dates
                            mikedorn

                            Posted 5 days ago...no response. Is my problem too difficult to solve or my DB not constructed well enough.

                            Is anybody helping?

                             

                            • 11. Re: summing a field for multiple years from a range of dates
                              philmodjunk

                              The people that respond to requests to help volunteer their time to help others. I can't speak for Sorbsbuster but I've been out of the office for a day and did not access this forum during that time. (Out SIck)

                              I'm pretty much starting from square one here, so apologies if I repeat issues already addressed. (I did skim the preceding posts, but it easy to miss stuff.)

                              What field type is Attendance_Tardy::Date_Code? (Check it in Manage | Database | Fields.)

                              I think you have set up this relationship. Please confirm or tell me what I got wrong:

                              Reviews_Tardy::__kf_Review_ID = Attendance_Tardy::__kf_Employee_ID AND
                              Reviews_Tardy::Const_T = Attendance_Tardy::Code
                              Reviews_Tardy::Date_End_Review > Attendance_Tardy::Date_Code
                              Reviews_Tardy::Date_Start < Attendance_Tardy::Date_Code

                              Const_T returns the same data type as Code?

                              and the fields with date in their names are either date fields or fields with a return type of "date"?

                              • 12. Re: summing a field for multiple years from a range of dates
                                mikedorn

                                Sorry to hear you where sick.

                                I've tried so many suggestions that I don't know what works and what doesn't work anymore. I'm frustrated, I applogize for being cranky. I just want this to work as I envisioned.

                                 

                                Answer:

                                What field type is Attendance_Tardy::Date_Code?

                                (Date)

                                Answer Second Part:

                                See Screen Shot Upload.

                                Reviews_Tardy::__kf_Review_ID = Attendance_Tardy::__kf_Employee_ID AND

                                Reviews_Tardy::Const_T = Attendance_Tardy::Code

                                Reviews_Tardy::Date_End_Review >= Attendance_Tardy::Date_Code     Date_End_Review = Date_Review (a date field on the review layout.)

                                Reviews_Tardy::Date_Start =< Attendance_Tardy::Date_Code     Date_Start = Date_Review - 365 (Calculation (Date result)

                                These are an entirely seperate set of TO's.




                                • 13. Re: summing a field for multiple years from a range of dates
                                  philmodjunk

                                  Does the "reviews layout" refer to Reviews_Tardy in Show Records from in Layout Setup?

                                  If you place a portal to Attendance_Tardy on your Review_Tardy layout, what do you see? Do you see the expected records or ???

                                  • 14. Re: summing a field for multiple years from a range of dates
                                    mikedorn

                                    Does the "reviews layout" refer to Reviews_Tardy in Show Records from in Layout Setup? Yes!

                                    If you place a portal to Attendance_Tardy on your Review_Tardy layout, what do you see? Do you see the expected records or ??? No

                                    The Review Layout shows only the first record of the "Reviews" table for each employee no matter what the year is on the review. If the data from 2011's review date is first on the related Attendance record it show that summation of records, if the data is from 2012's review and that review date is first on the related Attendance records then those summations show, etc.


                                    I've included the Attendance layout to show the fields and their placement.

                                    Ignore the numbers above the words "Attendance Summary" they are my experiment for now.


                                    1 2 Previous Next