Aggregated Sum

[#1]

Hi,

I have the following table (attached).

I need to develop a column (Backlog) which will be aggregated sum of the column (Old_Backlog) and looks like:

Date	Incoming	Outgoing	Backlog	Old_Backlog
01/05/2016	7,173	7,161	0	0
02/05/2016	88,735	86,638	12	12
03/05/2016	89,987	87,536	2,109	2097
04/05/2016	76,225	73,896	4,560	2451

Backlog of 03/05/2016 is Old_Backlog of 01/05/2016 + Old_Backlog of 02/05/2016+Old_Backlog of 03/05/2016.

Backlog of 04/05/2016 is Old_Backlog of 01/05/2016 + Old_Backlog of 02/05/2016+Old_Backlog of 03/05/2016 + Old_Backlog of 04/05/2016.

For this purpose i used function Aggr in the following way:

Backlog=

=sum(aggr(sum(Old_Backlog),Date))

but it does not work and shows data of Old_Backlog.

Please help.

Thanks,

Inna

[Kushal_Chawda · Accepted solution]

=Rangesum(above(total sum(Backlog),0,rowno()))

[Kushal_Chawda]

or in script like below

Data:

LOAD

Date

Incoming

Outgoing

Backlog

FROM Table;

New:

noconcatenate

LOAD *,

          if(Date<>previous(Date), Rangesum(Peek('Old_Backlog'),Backlog),Backlog) as Old_Backlog

resident Data

order by Date asc;

drop table Data;

[#4]

Hi Kushal,

actually I made a mistake in my question.

My table should look like:

Date	Incoming	Outgoing	Old_Backlog	Backlog
01/05/2016	7.173	7.161	12	12
02/05/2016	88.735	86.638	2097	2.109
03/05/2016	89.987	87.536	2451	4.560
04/05/2016	76.225	73.896	2329	6.889
05/05/2016	109.117	107.020	2097	8.986
06/05/2016	112.271	110.460	1811	10.797

i.e. to shift it one row above.

Please help.

[Kushal_Chawda]

try this

=Rangesum(above(total sum(Old_Backlog),0,rowno()))

[#6]

Nothing changed:(

[#7]

Hi Kushal,

actually this solution worked for me!

Thanks a lot!!!