topic Problem with Running Total in QlikView

Problem with Running Total

Mon, 11 Apr 2011 15:20:27 GMT

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.

Problem with Running Total

Miguel_Angel_Baeyens — Mon, 11 Apr 2011 15:32:11 GMT

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.

Problem with Running Total

Mon, 11 Apr 2011 15:36:17 GMT

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

Re: Problem with Running Total

Miguel_Angel_Baeyens — Mon, 11 Apr 2011 15:42:25 GMT

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.

Problem with Running Total

Mon, 11 Apr 2011 16:19:21 GMT

Hi Miguel Angel Baeyens,

You are a Genius. It worked for me.

Many Thanks,

Taj.

Problem with Running Total

Mon, 11 Apr 2011 17:41:00 GMT

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

Problem with Running Total

Miguel_Angel_Baeyens — Mon, 11 Apr 2011 18:37:58 GMT

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.

Problem with Running Total

Tue, 12 Apr 2011 16:47:14 GMT

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

Problem with Running Total

Miguel_Angel_Baeyens — Wed, 13 Apr 2011 05:40:22 GMT

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.

Problem with Running Total

Thu, 21 Apr 2011 10:52:33 GMT

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.

Problem with Running Total

Miguel_Angel_Baeyens — Fri, 22 Apr 2011 18:34:12 GMT

Hello Taj,

I don't have such QVW files. Maybe you can post some sample data or one file with only your chart and the expected behavior.

Regards.

Problem with Running Total

Thu, 04 Aug 2011 11:58:19 GMT

This solution is awesome!

THANK YOU!