10 Replies Latest reply: Jul 31, 2013 3:40 PM by Friedrich Hofmann RSS

    Summing up daily values to get a daily total?

    Friedrich Hofmann

      Hi,

       

      in principle I know what to do:

      - I am dealing with personell data and I want to build a diagram based on the nr. of hrs. of overtime.

      - I have a table where there is a value per day - the individual overtime for a certain emp_no on that day.

      => By summing that up until today without any timely restrictions, I have established that I can get a pretty accurate value.

      - I just want to do this in the script now and have one cumulative value per day.

      <=> Somehow, that does not work: I get only the individual day-values, never a total.

       

      My code currently is this:

      ÜS:

      LOAD

           Datum_ÜS,

           Pers_Nr,

           IF(Pers_Nr=previous('Pers_Nr'), (Previous('ÜS_akt')+ÜS_akt), ÜS_akt) as ÜS_akt_summ,

           ÜS_akt as ÜS_ind_Vgl

      RESIDENT Überstd;

       

      (where Überstd is already sorted first  by emp_no (Pers_Nr) asc, then by date (Datum_ÜS) asc.)

      Instead of a running total, I in the field labeled "ÜS_akt_summ", I get the same as in the field "ÜS_ind_Vgl" which tells me that the PEEK fct. is probybly never recognizing one emp_no as being exactly the same as in the record before.

      Can anybody help me get this straight? It can only be some little thing that I'm doing wrong I guess?

       

      Thanks a lot!

      Best regards,

       

      DataNibbler

        • Re: Summing up daily values to get a daily total?
          Gysbert Wassenaar

          Maybe like this:

           

          ÜS:

          LOAD

               Datum_ÜS,

               Pers_Nr,

               sum(ÜS_akt) as ÜS_akt_summ,

          RESIDENT Überstd

          GROUP BY Datum_ÜS,Pers_Nr;

            • Re: Summing up daily values to get a daily total?
              Friedrich Hofmann

              Hi Gysbert,

               

              You're pretty close - but not quite there: Yes, of course that would work - but it would only give me ONE total which would always be the sum of all the values in the table up to the present day.

              I want instead to be able to select any given day and see how many hours of overtime (cumulative) that emp´_no had on that very day. That is why I had the idea of using the PEEK (or PREVIOUS) function to sum the records up.

               

              Best regards,

               

              DataNibbler

                • Re: Summing up daily values to get a daily total?
                  Gysbert Wassenaar
                  but it would only give me ONE total which would always be the sum of all the values in the table up to the present day.

                  No, it gives you the sum per day per employee: GROUP BY Datum_ÜS,Pers_Nr;

                    • Re: Summing up daily values to get a daily total?
                      Friedrich Hofmann

                      Hi Gysbert,

                       

                      yes - it would give me ONE total per employee, always the current one for that employee. Right?

                      That's not what I want in this diagram, however. I don't want to display just a snapshot of the present day, but a history - I want to be able to display the nr._of_hrs_overtime an employee had, say, a week ago and to compare it week over week. That's why I wanted to create a running total. I could do it with just ONE current total by storing the results every day, but that's not what I want to do.

                      Well, I'll quickly try it out. Maybe I'm misunderstanding you.

                        • Re: Summing up daily values to get a daily total?
                          Friedrich Hofmann

                          Hi Gysbert,

                           

                          no, I guess it's still not working:

                          Your proposal works, but on every date, that gives me only the sum of all entries that PN has ON THAT DAY.

                          An example I'm currently looking at is one PN that, by chance, has only one entry on that day - there might always be several.

                          - That PN's value on that day is 2 - 2hrs of overtime on the 1st of July 2013.

                          -> Your proposal with the SUM will also return a value of 2 on that day.

                          <=> That is not that PN's total, only an individual day's value. That PN should, on that day (= up_to_that_day) have a total of about 50 hrs of overtime.

                          => That's why I want to sum up all the records in that table up to a certain date - a running total that will, on every given day, return me the total of hrs of overtime that PN has amassed up to that day.

                           

                          Thanks a lot for helping!

                          I hope I have explained quite precicely what I want to do and exactly why your proposal does not work for me. If not, feel free to ask ;-)

                          It's my last week prior to the summer holidays and I'm somewhat anxious to get this done and get this app out before I'm gone...

                          Best regards,

                           

                          DataNibbler

                            • Re: Summing up daily values to get a daily total?
                              Friedrich Hofmann

                              Hi,

                               

                              I've had another read in "QlikView 11 for Developers" and I've done exactly as they do in this instance - I use the PEEK fct inside an IF clause to determine whether I'm still looking at the same PN (group of records) and if so, I add that record's value to the last <-> when i encounter a new group, I just use the current value as a starting point.

                              I'm absolutely puzzled - I still get the exact same value in my summed-up field than in the original field - which, in fact, can only mean that for some reason one PN is not recognized as the same as that in the row above - but it is - I am ordering the table in the same step and it should have a nr. of records for the same PN.

                               

                              My code is this currently - maybe someone can spot an error?

                               

                              ÜS:

                              LOAD

                                   Datum_ÜS,

                                   Pers_Nr,

                                   IF(Pers_Nr=Peek('Pers_Nr', -1), ÜS_akt + PEEK('ÜS_akt', -1), ÜS_akt) as ÜS_akt_summ

                              RESIDENT Überstd_akt

                              Order BY Pers_Nr asc, Datum_ÜS_orig asc;

                               

                              (I order by a different field: "Datum_ÜS_orig" is what comes out of the database, it looks like >20130620<, but it's a STRING - so I have to rearrange it to get a valid date. Just to avoid any error potential here, I order using the original field.)

                               

                              Thanks a lot!

                              Best regards,

                               

                              DataNibbler

                    • Re: Summing up daily values to get a daily total?

                      Hi Friedrich,

                      it seems that you need a line in your script like this:

                       

                      If(Kunde=Peek(Kunde),RangeSum(Bestand,Peek(Überstunden)),Bestand) as Überstunden

                       

                      Please notice that that the field name you use in the PEEK function is the name of your new accumulate field. Otherwise you will get the numbers of the original field, as you described.

                      See the attached example for help!

                       

                      Good luck!

                       

                       

                      Rainer

                        • Re: Summing up daily values to get a daily total?
                          Friedrich Hofmann

                          Hi Rainer,

                           

                          well, that looks good. It is something I haven't yet thought of. I will look that RangeSum function up. It is logical that I have to use PEEK with the new field, not with the old ;-) It would be really  cool if I could get that going.j

                           

                          P.S.: I cannot really make head or tail of your example - it doesn't seem to be working? The field "Überstunden" which you calculate is the same as "Bestand", no matter what date I select...

                           

                          However, it does seem to work - I get much more sensible figures now. Unfortunately, I can only really check against the database when I have someone from HR sitting with me. Well, let's see as the blind say...

                            • Re: Summing up daily values to get a daily total?
                              Friedrich Hofmann

                              Hi Rainer,

                               

                              another hurdle:

                              In the database I query currently, not every PN has records for every day at all (people do go on holidays even in my company).

                              => That means that, depending on the date I select, I have a different number of (distinct) personell_numbers.

                              => That, of course, messes up my entire diagram

                              => I need to fill up the table so every personell_number gets a record for every day - with a 0, so the respective nr. of hrs of overtime does not change when someone wasn't there.

                              I will start off by studying that document I downloaded here on that issue and I'll see what can be done.

                              Can you maybe lend me a hand to get that straight?

                               

                              Thanks a lot!

                              Best regards,

                               

                              DataNibbler

                               

                              P.S.: In principle, that method is quite easy. It's roughly the same method we have employed to generate our master_calendar.

                                       The problem in this instance is that I have not one, but a large nr. of min_dates and max_dates - I have already filtered for those employees who are still with us and I guess the max_date would always be TODAY() - but min_date might  be diffferent for each employee.

                              I guess I'll have to build a loop over all the personell_numbers I have.

                              I could do this along the lines of that insurance-example in the document:

                              - In the employee_masterdata table, every employee has a start_date and an end_date which is usually some far-future date. I could use that table as a base to then generate all the dates inbetween. I guess I will need that info anyway.

                              -> Then I could join that with my other table. Many of those dates will probably be in there, but not all.

                              <=> I have to introduce an IF clause in my master_table: Many emps have an end_date of "21001231" or so in that table. there's no point in autogenerating every day until then. I need them only up to today since I only take into account emps who are still with us anyway.

                                • Re: Summing up daily values to get a daily total?
                                  Friedrich Hofmann

                                  Hi,

                                   

                                  can anyone help me once more?

                                  - I have now used an employee_masterdata table with an entry_date and a leaving_date

                                  => I have enlarged that so there is now one record per PN for every day inbetween.

                                  - I have joined the PN and the date from that table with what I had before (overtime data).

                                  => There were then a lot of empty rows on the days that were not originally present in the overtime_table.

                                  => These I filled up using an an IF-clause:

                                       >>  IF(ISNULL(date)=-1, 0, date)  <<

                                   

                                  - I then ordered the entire table by PN (asc.) and then by date (asc.) and used the RangeSum function like Rainer proposed.

                                  Now, however, I get way to high values. There must be something wrong.

                                  For illustration, this is the code I'm currently testing:

                                   

                                  Überstd_akt:
                                  LOAD
                                      (RIGHT(trim(datumn), 2) & '.' & MID(trim(datumn), 5, 2) & '.' & LEFT(trim(datumn), 4)) as Datum_ÜS,
                                      datumn as Datum_ÜS_orig,
                                      pnr as Pers_Nr5,
                                      num( ((LEFT(wertakt, (Len(wertakt)-2)))/100), '#.##0,00') as ÜS_akt
                                  WHERE  (RIGHT(datumvon, 2) & '.' & MID(datumvon, 5, 2) & '.' & LEFT(datumvon, 4)) <= TODAY()
                                  AND   (RIGHT(datumbis, 2) & '.' & MID(datumbis, 5, 2) & '.' & LEFT(datumbis, 4)) >= TODAY()
                                  //  and   (RIGHT(datumn, 2) & '.' & MID(datumn, 5, 2) & '.' & LEFT(datumn, 4)) >= YearStart(TODAY())
                                  ;

                                  SQL SELECT

                                  [database query]

                                  ;

                                  // Joining with a table where there is every date (between joining_date and leaving_date) for every employee

                                   

                                  LEFT JOIN (Überstd_akt)

                                   

                                  LOAD
                                      Pers_Nr as Pers_Nr5,
                                      Datum_HR as Datum_ÜS
                                  Resident MA_Daten_tgl
                                  ;

                                   

                                  // Then I filled up the NULL values created by that JOIN with 0

                                   

                                  ÜS_akt:
                                  LOAD
                                      Pers_Nr5,
                                      Datum_ÜS,
                                      IF(ISNULL(ÜS_akt)=-1, 0, ÜS_akt) as ÜS_ind
                                  Resident Überstd_akt;

                                   

                                  DROP TABLE Überstd_akt;

                                   

                                  // This is where I was planning to use the PEEK function

                                   

                                  ÜS_ges:
                                  LOAD
                                      Pers_Nr5,
                                      Datum_ÜS,
                                      IF(Pers_Nr5=Peek('Pers_Nr5'), RangeSum(Peek(ÜS_sum), ÜS_ind), ÜS_ind) as ÜS_sum,
                                      ÜS_ind as ÜS_Vgl
                                  Resident ÜS_akt
                                  ORDER BY Pers_Nr5 asc, Datum_ÜS asc
                                  ;

                                   

                                  Thanks a lot!

                                  Best regards,

                                   

                                  DataNibbler

                                   

                                  P.S.: I looked at the table in the table_viewer and it is obvious that I get a cartesian product through the JOIN: Every date from my "enlarged_masterdata" table is joined to every date (within the same PN) in that time-values table. That way, instead of the 600k or 700k that I would expect, I get 32mio records.

                                  Only I don't understand why, the dates are all derived in the same manner - they all come out of the database as a STRING and I rearrange them to get the german date format.