Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 ?
May be this
Aggr(
RangeSum(Above(Sum(Amount), 0, RowNo()))
, [Klant nr.], [%WerkDagDatum])
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)
May be this
Aggr(
RangeSum(Above(Sum(Amount), 0, RowNo()))
, [Klant nr.], [%WerkDagDatum])
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
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
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)
Would you be able to share the app so that we can see the script and chart?
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)