Problem with Running Total

[#1]

Hi,

I have a table containing the sum of all payments made by a customer for each month. I want to add another column to the table to show the running total of all payments upto and including that month. Please can some one help?

As you can see, the current table below contains the 'Payment this month'. I just want to maintain a running total on a new column as per the second table. i.e. the current month should also have the payment made this month and all other previous months payments.

Many Thanks,

Taj.

[Miguel_Angel_Baeyens]

Hello,

In a chart, you can get that done using inter-record functions, according to your table, your new expression in a pivot or straight table should look like

Sum(Payment) + above([Payment This Month])

Where "Sum(Payment)" is the expression labelled "Payment this Month".

This will work as well

RangeSum(Above([Payment], 0, RowNo()))

Hope that helps.

[#3]

Hi Migual Angel Baeyens,

Thanks for your reply. Please can you let me know how to do it as part of the script? I would like a new column in the table instead.

Many Thanks.

Taj

[Miguel_Angel_Baeyens · Accepted solution]

Hi,

I've replied a post with something very similar, but basically, using Previous() and Peek() functions in it:

Table:
LOAD IterNo() AS Month, 
     Ceil(Rand() * 1000) AS Amount
AUTOGENERATE 1 WHILE IterNo() < 13; 
TableStep2:
NOCONCATENATE LOAD *
RESIDENT Table
ORDER BY Month; 
DROP TABLE Table; 
AcumTable:
LOAD *, 
     If(Month > Previous(Month), RangeSum(Amount, Peek('MonthAcum')), Amount) AS MonthAcum
RESIDENT TableStep2; 
DROP TABLE TableStep2;

For each record, if current record's Month field value is greater than previous record's, then add previous amount to current, and store it in a new field called "MonthAcum". If the record loaded is the first (so there is no previous) then the value is its own Amount.

Hope that helps.

[#5]

Hi Miguel Angel Baeyens,

You are a Genius. It worked for me.

Many Thanks,

Taj.

[#6]

Hi Miguel Angel Baeyens,

I am struggling with another problem now. I have different client accounts having amounts on the same month end and the logic is not able to differenciate between clients accounts and is mixing amounts between clients accounts. For example, if I have payments as per the below table, the logic adds the amounts across the clients and ends up with incorrect amount. Please can you let me know how I have differenciate clients while using the rangesum?

Thanks,

Taj

[Miguel_Angel_Baeyens]

Hello Taj,

You need to add another field in the ORDER BY clause as well as in the conditional, to look like the following (note that since these are rand data, they may return unexpected results)

Table:LOAD Chr(64 + Ceil(Rand() * 3)) AS CustNo, IterNo() AS Month, Ceil(Rand() * 1000) AS AmountAUTOGENERATE 3 WHILE IterNo() < 13; TableStep2:NOCONCATENATE LOAD *RESIDENT TableORDER BY CustNo, Month; DROP TABLE Table; AcumTable:LOAD *, If(CustNo = Previous(CustNo), If(Month > Previous(Month), RangeSum(Amount, Peek('MonthAcum')), Amount), Amount) AS MonthAcumRESIDENT TableStep2; DROP TABLE TableStep2;

Hope that helps.

[#8]

Hi Miguel Angel Baeyens,

I have another problem. This time I want to display the running totals for all months where payment was not made and I tried various load options(i.e. Outer, Inner etc.) and I am not getting the expected result. I have two tables as per the below and I am expecting the result as per the third table. Please can you let me know how I can achieve the result?

Thanks,

Taj

[Miguel_Angel_Baeyens]

Hello Taj,

If your calendar and your model is working fine, I'd draw two charts with the following expressions in the first (only months greater than zero):

Sum({< PayMonthEnd = {"=Sum(PayAmount) > 0"} >} PayAmount)Sum({< PayMonthEnd = {"=Sum(PayAmount) > 0"} >} PayToDate)

and

Sum(PayToDate)

In the second (all).

Hope that helps.

[#10]

Hi Miguel Angel Baeyens,

Sorry for this late response. I have tried your suggestion and below is the result that I am getting. Please can you pass me a simple qvw file with charts for me to look at?

Many Thanks,

Taj.