topic Re: Rangesum aggregation script in QlikView

Rangesum aggregation script

Thu, 03 Oct 2013 14:18:40 GMT

I have a table that contains

Meter Number	Days in service	Date	Daily Production
1	1	01/01/2010	11
1	2	02/01/2010	13
1	3	03/01/2010	18
1	4	04/01/2010	11
1	5	05/01/2010	12
2	1	03/01/2010	5
2	2	04/01/2010	16
2	3	05/01/2010	20
3	1	02/01/2010	17
3	2	03/01/2010	6
3	3	04/01/2010	7
3	4	05/01/2010	13
3	5	06/01/2010	7

I want to add accumulate the daily production column by meter number. I am not bothered if this is in the script or on a chart.

I am sure I have all the igrediants to make this possible, however none of the example Rangesums, aggregation examples seem to work for me. Please advise

The table should read

Meter Number	Days in service	Date	Daily Production	Total_Produced
1	1	01/01/2010	11	11
1	2	02/01/2010	13	24
1	3	03/01/2010	18	42
1	4	04/01/2010	11	53
1	5	05/01/2010	12	65
2	1	03/01/2010	5	5
2	2	04/01/2010	16	21
2	3	05/01/2010	20	41
3	1	02/01/2010	17	17
3	2	03/01/2010	6	23
3	3	04/01/2010	7	30
3	4	05/01/2010	13	43
3	5	06/01/2010	7	50

Many thanks in advance

Re: Rangesum aggregation script

Gysbert_Wassenaar — Thu, 03 Oct 2013 15:49:11 GMT

See attached example. The inline load is just an example to load some sample data. Replace it with your actual load statement.

Re: Rangesum aggregation script

Thu, 03 Oct 2013 16:02:08 GMT

Many Thanks

Worked a treat

Re: Rangesum aggregation script

Tue, 03 Dec 2013 15:31:04 GMT

Can u tel me the exact use of rangesum function.

Re: Rangesum aggregation script

angelaecheverri — Tue, 03 Dec 2013 18:19:49 GMT

Try loading your script using Peek function:

First Load your Table, using a name for it. Example:

Original_Base:

Load

Meter_Number,

Day_in_service,

Date,

Daily_Production

From

xxxx

then make a resident table, using Peek function and Order by.... like this....

Final_Base:

Load

Meter_Number as Meter_Number_2,

Day_in_service as Day_In_Servivce_2,

Date as Date_2,

Daily_Production as Daily_Production_2,

Peek(Daily_Production) + Daily_Production as Total_Produced

Resident

Original_Base

Order by Date asc;

note: you dont need to rename your new fields... but i like to do it in order to compare results...

if you dont do it at the end you need it to make a drop table sentence....

Drop table Original_Base;

Re: Rangesum aggregation script

angelaecheverri — Tue, 03 Dec 2013 18:25:27 GMT

If you want to use range sum

you can try:

RangeSum(Above(Sum(Sales), 0, RowNo()))

RangeSum(Above(TOTAL Sum(Sales), 0,RowNo(TOTAL)))

The first one acum your sales by dimension.

The second one acum without thinking of change dimensions....

Look the image....

Re: Rangesum aggregation script

Nagaraju_KCS — Tue, 03 Dec 2013 18:46:30 GMT

Hi Richard,

Try this....

Rangesum(above(sum([Daily Production]),0,rowno(total)))

Re: Rangesum aggregation script

Anonymous — Mon, 16 Dec 2013 18:54:37 GMT

Powerful function this Rangesum.

saved some coding for me

Thanks Gysbert