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.
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.
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...
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?
IF(Pers_Nr=Peek('Pers_Nr', -1), ÜS_akt + PEEK('ÜS_akt', -1), ÜS_akt) as ÜS_akt_summ
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!
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!
Lagerbestand.qvw 209.5 K
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...
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!
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.
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:
(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())
// Joining with a table where there is every date (between joining_date and leaving_date) for every employee
LEFT JOIN (Überstd_akt)
Pers_Nr as Pers_Nr5,
Datum_HR as Datum_ÜS
// Then I filled up the NULL values created by that JOIN with 0
IF(ISNULL(ÜS_akt)=-1, 0, ÜS_akt) as ÜS_ind
DROP TABLE Überstd_akt;
// This is where I was planning to use the PEEK function
IF(Pers_Nr5=Peek('Pers_Nr5'), RangeSum(Peek(ÜS_sum), ÜS_ind), ÜS_ind) as ÜS_sum,
ÜS_ind as ÜS_Vgl
ORDER BY Pers_Nr5 asc, Datum_ÜS asc
Thanks a lot!
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.