6 Replies Latest reply: Jun 2, 2016 5:18 AM by Inna Shnaiderman

# Aggregated Sum

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.

Thanks,

Inna

• ###### Re: Aggregated Sum

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

• ###### Re: Aggregated Sum

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.

• ###### Re: Aggregated Sum

try this

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

• ###### Re: Aggregated Sum

or in script like below

Data:

Date

Incoming

Outgoing

Backlog

FROM Table;

New:

noconcatenate

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

resident Data

order by Date asc;

drop table Data;

• ###### Re: Aggregated Sum

Hi Kushal,

actually this solution worked for me!

Thanks a lot!!!