Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
=Rangesum(above(total sum(Backlog),0,rowno()))
=Rangesum(above(total sum(Backlog),0,rowno()))
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;
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.
try this
=Rangesum(above(total sum(Old_Backlog),0,rowno()))
Nothing changed:(
Hi Kushal,
actually this solution worked for me!
Thanks a lot!!!