topic Re: Aggregated Sum in QlikView

Aggregated Sum

Thu, 02 Jun 2016 07:30:42 GMT

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

Re: Aggregated Sum

Kushal_Chawda — Thu, 02 Jun 2016 07:32:54 GMT

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

Re: Aggregated Sum

Kushal_Chawda — Thu, 02 Jun 2016 07:36:20 GMT

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;

Re: Aggregated Sum

Thu, 02 Jun 2016 08:56:53 GMT

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.

Re: Aggregated Sum

Kushal_Chawda — Thu, 02 Jun 2016 09:02:45 GMT

try this

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

Re: Aggregated Sum

Thu, 02 Jun 2016 09:07:38 GMT

Nothing changed:(

Re: Aggregated Sum

Thu, 02 Jun 2016 09:18:21 GMT

Hi Kushal,

actually this solution worked for me!

Thanks a lot!!!