How to ignore values when a Sum() reaches a certain limit

[wcushy]

Hi

I am writing a report that calculates how many hours an individual works on a given day, but want to ignore all remaining values once the sum of those hours reaches 160. I just don't know how to do this in either a select or load statement (I don't want to do this within a chart function).

The data needs grouping by employee number. Example data below. I only want the data for the first 23 rows of Employee 53 but all the rows for employee 56.

Anybody have any idea how best to do this. my data is stored in a .XLSX file.

Emp No	Forename	Surname	Date	Hours
53	Joe	Bloggs	01/01/2015	8
53	Joe	Bloggs	02/01/2015	8
53	Joe	Bloggs	17/02/2015	8
53	Joe	Bloggs	18/02/2015	8
53	Joe	Bloggs	19/02/2015	8
53	Joe	Bloggs	16/03/2015	4
53	Joe	Bloggs	02/04/2015	8
53	Joe	Bloggs	05/04/2015	8
53	Joe	Bloggs	04/05/2015	8
53	Joe	Bloggs	07/05/2015	4
53	Joe	Bloggs	22/05/2015	4
53	Joe	Bloggs	25/05/2015	8
53	Joe	Bloggs	26/05/2015	8
53	Joe	Bloggs	27/05/2015	8
53	Joe	Bloggs	28/05/2015	8
53	Joe	Bloggs	29/05/2015	8
53	Joe	Bloggs	10/07/2015	4
53	Joe	Bloggs	24/07/2015	4
53	Joe	Bloggs	17/08/2015	8
53	Joe	Bloggs	18/08/2015	8
53	Joe	Bloggs	19/08/2015	8
53	Joe	Bloggs	20/08/2015	8
53	Joe	Bloggs	21/08/2015	4
53	Joe	Bloggs	23/08/2015	8
53	Joe	Bloggs	24/08/2015	8
53	Joe	Bloggs	25/08/2015	8
53	Joe	Bloggs	26/08/2015	8
53	Joe	Bloggs	27/08/2015	8
53	Joe	Bloggs	31/08/2015	8
53	Joe	Bloggs	30/09/2015	4
53	Joe	Bloggs	28/10/2015	8
53	Joe	Bloggs	29/10/2015	8
53	Joe	Bloggs	06/11/2015	8
56	Fred	Bloggs	31/12/2014	8
56	Fred	Bloggs	01/01/2015	8
56	Fred	Bloggs	30/03/2015	8
56	Fred	Bloggs	31/03/2015	8
56	Fred	Bloggs	01/04/2015	8
56	Fred	Bloggs	02/04/2015	8
56	Fred	Bloggs	03/04/2015	8
56	Fred	Bloggs	06/04/2015	8
56	Fred	Bloggs	03/05/2015	8
56	Fred	Bloggs	24/05/2015	8
56	Fred	Bloggs	20/07/2015	8
56	Fred	Bloggs	21/07/2015	8
56	Fred	Bloggs	22/07/2015	8

[Gysbert_Wassenaar · Accepted solution]

You can calculate a cumulative hours field and use that in a where clause in a preceding load:

Data:

LOAD * Where CumulativeHours <= 160;

LOAD *, If([Emp No]=Previous([Emp No]),rangesum(Hours, Peek(CumulativeHours)),Hours) as CumulativeHours;

LOAD [Emp No], Forename, Surname, Date, Hours FROM ...source...;

If your source data isn't ordered by employee and date then you first need to create a table that sorts the data:

Temp:

LOAD [Emp No], Forename, Surname, Date, Hours FROM ...source...;

Data:

LOAD * Where CumulativeHours <= 160;

LOAD *, If([Emp No]=Previous([Emp No]),rangesum(Hours, Peek(CumulativeHours)),Hours) as CumulativeHours;

LOAD [Emp No], Forename, Surname, Date, Hours

Resident Temp

Order By [Emp No], Date;

Drop Table Temp;

[wcushy]

Thanks Gysbert.

This does work, but not if i have a Qualify statement before the load expression. I have found a workaround to it though so it is now working for me.

Just got to look at selecting a date range based on selected date now with in my report.

Regards

Wayne