Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld 2020: Join us May 11 - 14, 2020 in Phoenix, AZ. Register early and save $400. Learn More
Not applicable

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:

   

DateIncomingOutgoingBacklogOld_Backlog
01/05/20167,1737,16100
02/05/201688,73586,6381212
03/05/201689,98787,5362,1092097
04/05/201676,22573,8964,5602451

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

1 Solution

Accepted Solutions

Re: Aggregated Sum

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

View solution in original post

6 Replies

Re: Aggregated Sum

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

View solution in original post

Re: Aggregated Sum

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;

Not applicable

Re: Aggregated Sum

Hi Kushal,

actually I made a mistake in my question.

My table should look like:

DateIncomingOutgoingOld_BacklogBacklog
01/05/20167.1737.1611212
02/05/201688.73586.63820972.109
03/05/201689.98787.53624514.560
04/05/201676.22573.89623296.889
05/05/2016109.117107.02020978.986
06/05/2016112.271110.460181110.797

i.e. to shift it one row above.

Please help.

Re: Aggregated Sum

try this


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

Not applicable

Re: Aggregated Sum

Nothing changed:(

Not applicable

Re: Aggregated Sum

Hi Kushal,

actually this solution worked for me!

Thanks a lot!!!