Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Highlighted
katwijck
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:

QV 1.png

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)
2 Solutions

Accepted Solutions

Re: Sum of current + previous rows

May be this

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

Re: Sum of current + previous rows

This could be one solution

image.png

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
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

Re: Sum of current + previous rows

May be this

Aggr(
  RangeSum(Above(Sum(Amount), 0, RowNo()))
, [Klant nr.], [%WerkDagDatum])
katwijck
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

katwijck
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.

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

katwijck
New Contributor III

Re: Sum of current + previous rows

Hi Sunny,

I have created a As-Of calendar for days, see below:

QV 2.png

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:

QV 3.png

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:

QV 4.png

The following expression is used in the chart: sum({$<DateDiff={"<=14"}>}Delivery)

Re: Sum of current + previous rows

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

Re: Sum of current + previous rows

This could be one solution

image.png

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)

 

katwijck
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