Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

datanibbler
Esteemed Contributor

Summing up daily values to get a daily total?

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

10 Replies

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

Maybe like this:

ÜS:

LOAD

     Datum_ÜS,

     Pers_Nr,

     sum(ÜS_akt) as ÜS_akt_summ,

RESIDENT Überstd

GROUP BY Datum_ÜS,Pers_Nr;


talk is cheap, supply exceeds demand
datanibbler
Esteemed Contributor

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

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?

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;


talk is cheap, supply exceeds demand
datanibbler
Esteemed Contributor

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

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.

Highlighted
datanibbler
Esteemed Contributor

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

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

datanibbler
Esteemed Contributor

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

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

Not applicable

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

datanibbler
Esteemed Contributor

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

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...

datanibbler
Esteemed Contributor

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

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.