# QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
New Contributor III

## Sum of current + previous rows

Hi,

I am looking to achieve the following but am not sure how to achieve this.

I have created the table as below: Now I want to add the sum of the current "Outstanding Amount" value for a customers row and of the previous ones in a separate column (Total Outstanding Amount).

So on the 2nd row, the value of this column would be 5.444,88+43.432,67=48.877,55

On the 3rd row that would then be 107.593,08+5.444,88+43.432,67 etc.

I have tried RangeSum with Above but was not able to achieve this.

How should I approach this ?

Labels (1)
• ### Scripting

2 Solutions

Accepted Solutions MVP

## Re: Sum of current + previous rows

May be this

``````Aggr(
RangeSum(Above(Sum(Amount), 0, RowNo()))
, [Klant nr.], [%WerkDagDatum])`````` MVP

## Re: Sum of current + previous rows

This could be one solution Dimensions

``````Klant nr.
AsOfDate``````

Expressions

``````Amount due
Aggr(RangeSum(Above(Sum(If(TEST = AsOfDate, [Outstanding Amount])),0,RowNo())), [Klant nr.], AsOfDate)+Saldo

To be delivered
Aggr(RangeSum(Above(Sum(If(TEST = AsOfDate, Delivery)),0,RowNo())), [Klant nr.], AsOfDate)

Future
Sum({\$<DateDiff={"<=14"}>}Delivery)``````

9 Replies Partner

## Re: Sum of current + previous rows

What was the issue with RangeSum and Above? that in my opinion is the way to go
please share expression you tried and issue MVP

## Re: Sum of current + previous rows

May be this

``````Aggr(
RangeSum(Above(Sum(Amount), 0, RowNo()))
, [Klant nr.], [%WerkDagDatum])``````
New Contributor III

## Re: Sum of current + previous rows

Hi Sunny,

After changing the "Amount" field with the "Outstanding Amount" field this did the trick!

Thank you for this.

I slightly changed the expression like below to calculate the "future" which is determined by the "Delivery" field:

Aggr(RangeSum(Below(Sum(Delivery),0,RowNo())), [Klant nr.], [%WerkDagDatum])

How should I modify this so that I can sum the amount of "Delivery" until 14 days of the current row date value ?

With kind regards,

Martin van der Bent

New Contributor III

## Re: Sum of current + previous rows

Hi Dilipranjith,
I ended with the value of "0" because I didn't use the Aggr function as mentioned by Sunny below. MVP

## Re: Sum of current + previous rows

14 days can get tricky if you don't have continuous dates for each customer... For 14 days, I would suggest using The As Of Table

New Contributor III

## Re: Sum of current + previous rows

Hi Sunny,

I have created a As-Of calendar for days, see below: If I select the AsOfDate of the 15th of June, I see all the future TEST dates which have a date difference until 14 days.

The applicable rows are also selected in the data table: But how can I achieve to have the "To be delivered" value of the last row (which is the sum of all the deliveries) to be shown on the first row in this example ?

The next row should show the sum of "To be delivered" until the 30th of June etc.

When I create a separate chart it calculates the sum as I would like to have added to the chart above: The following expression is used in the chart: sum({\$<DateDiff={"<=14"}>}Delivery) MVP

## Re: Sum of current + previous rows

Would you be able to share the app so that we can see the script and chart? MVP

## Re: Sum of current + previous rows

This could be one solution Dimensions

``````Klant nr.
AsOfDate``````

Expressions

``````Amount due
Aggr(RangeSum(Above(Sum(If(TEST = AsOfDate, [Outstanding Amount])),0,RowNo())), [Klant nr.], AsOfDate)+Saldo

To be delivered
Aggr(RangeSum(Above(Sum(If(TEST = AsOfDate, Delivery)),0,RowNo())), [Klant nr.], AsOfDate)

Future
Sum({\$<DateDiff={"<=14"}>}Delivery)``````

New Contributor III

## Re: Sum of current + previous rows

Hi Sunny,

It seems to (again) do the trick!
Thank you very much!

With kind regards,

Martin van der Bent