3 Replies Latest reply on Oct 16, 2012 8:52 AM by philmodjunk

    Sum of summaries from related table

    otto_m

      Title

      Sum of summaries from related table

      Post

           Hi,

           In order to keep track of jobs of individual employees for a given period, I use these fields:

           HRSWEEK (Number)
           FACTOR (Unstored calculation (Case function to accommodate different activities and seniority))
           WEEKS (Number from related table)
           EMP (unique employee ID) 

           EARN (is the result of  HRSWEEK * WEEKS * FACTOR * 80 (the current hourly rate)). This works fine for each individual job.

           EARNSUM (Total of EARN (running with restart when sorted by EMP)) - This works fine in an overview of all employees.

           Now my problem is that I need to keep a separate file for each period, and I need a field that is the sum of EARNSUM of the previous file and the current file.

           I tried to create a new field using Sum (EARNSUM ; Q2::EARNSUM), but the calculation is way off.

           I also tried GetSummary for both, but it does not yield the correct result either.

           What do I need to do to get a correct sum of the Totals of both the current and the previous file?

           As always, thanks in advance for your assistance,

           Otto 

        • 1. Re: Sum of summaries from related table
          philmodjunk
               

                    Now my problem is that I need to keep a separate file for each period

               Why split this data into separate files? There are many ways to manage your data by time period without needing to split the data up like this and doing so will greatly complicate managing your data--this post being just the first example of that.

          • 2. Re: Sum of summaries from related table
            OttoMaderdonner

                 Thank you for your quick comment. Sorry it took so long to reply to your suggestion.

                 The reason why data is split into separate files is that I inherited this job from a former colleague who kept each time period in a separate file.

                 I would really be grateful if you could tell me how to better organise the data so I don't hit snags as the one described above.

                  

                 As always, thanks in advance for your assistance,
                  
                 Otto
            • 3. Re: Sum of summaries from related table
              philmodjunk

                   You'll need to analyze your current design to figure out why it was split this way in the first place. That's not something I can do from here.

                   You might take a copy of your system and see what happens if you import data to combine data from two or more years in the same file and then experiment with this copy to see what issues develop. Generally speaking, you can set up scripts, relationships, portal filters, and so forth that can limit the records shown by the year so that you can see only the current year's data or only the data from a year specified by you.