topic Re: Sum of current + previous rows in QlikView

Sum of current + previous rows

katwijck — Wed, 12 Jun 2019 12:01:15 GMT

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 ?

Re: Sum of current + previous rows

dplr-rn — Tue, 11 Jun 2019 14:48:09 GMT

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

sunny_talwar — Tue, 11 Jun 2019 15:02:51 GMT

May be this

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

Re: Sum of current + previous rows

katwijck — Tue, 11 Jun 2019 19:04:45 GMT

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

Re: Sum of current + previous rows

katwijck — Tue, 11 Jun 2019 19:06:35 GMT

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

sunny_talwar — Tue, 11 Jun 2019 19:14:54 GMT

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

Re: Sum of current + previous rows

katwijck — Wed, 12 Jun 2019 11:37:49 GMT

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)

Re: Sum of current + previous rows

sunny_talwar — Wed, 12 Jun 2019 11:41:32 GMT

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

Re: Sum of current + previous rows

sunny_talwar — Wed, 12 Jun 2019 12:37:15 GMT

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)

Re: Sum of current + previous rows

katwijck — Wed, 12 Jun 2019 14:36:30 GMT

Hi Sunny,

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

With kind regards,

Martin van der Bent