Sum of current + previous rows

[katwijck]

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 ?

[dplr-rn]

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

[sunny_talwar · Accepted solution]

May be this

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

[katwijck]

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]

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

[sunny_talwar]

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]

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)

[sunny_talwar]

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

[sunny_talwar · Accepted solution]

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)

[katwijck]

Hi Sunny,

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

With kind regards,

Martin van der Bent