Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Running Total Calculation

I need to calculate a running total in my script.  My data set looks like the first 3 columns of this table:

LocationDateRevenueRunning Total
AJan-13 $  1,000 $ 1,000
AFeb-13 $   1,100 $ 2,100
AMar-13 $   1,200 $ 3,300
AApr-13 $   1,300 $ 4,600
BJan-13 $   1,400 $ 1,400
BFeb-13 $   1,500 $ 2,900
BMar-13 $   1,600 $ 4,500
BApr-13 $   1,700 $ 6,200
CJan-13 $   1,800 $ 1,800
CFeb-13 $   1,900 $ 3,700
CMar-13 $  2,000 $ 5,700
CApr-13 $   2,100 $ 7,800

I want to calculate the fourth column - a running total of Revenue grouped by column 'Location'. Any ideas ?  I'm going to be using this in a pivot table on the front end.

1 Solution

Accepted Solutions
MarcoWedel

Hi,

or given a table

tab1:

LOAD Location,

     Date#(Date, 'MMM-YY') as Date,

     Num#(Revenue, '$ 0,000') as Revenue

FROM [http://community.qlik.com/thread/121052] (html, codepage is 1252, embedded labels, table is @1);

by joining the Running Total column like:

Left Join (tab1)

LOAD Location,

     Date,

     Num(If(Location=Peek(Location), Revenue+Peek([Running Total]), Revenue), '$ 0,000') as [Running Total]

Resident tab1

Order By Location, Date;

QlikCommunity_Thread_121052_Pic1.JPG.jpg

hope this helps

regards

Marco

View solution in original post

13 Replies
MK_QSL
MVP
MVP

Temp:

Load

  Location,

  Date#(Date,'MMM-YY') as Date,

  Revenue

Inline

[

  Location, Date, Revenue

  A, Jan-13,   1000

  A, Feb-13,    1100

  A, Mar-13,    1200

  A, Apr-13,    1300

  B, Jan-13,    1400

  B, Feb-13,    1500

  B, Mar-13,    1600

  B, Apr-13,    1700

  C, Jan-13,    1800

  C, Feb-13,    1900

  C, Mar-13,   2000

  C, Apr-13,    2100

];

Final:

Load

  Location,

  Date,

  Revenue,

  If(Location = Previous(Location), RangeSum(Revenue + Peek('Running Total')), Revenue) as [Running Total]

Resident Temp

Order By Location, Date;

Drop Table Temp;

its_anandrjs

You can try this way also

tmp:

LOAD *,if(Location = Previous(Location), (Revenue + Peek(Running_Total)),Revenue) as Running_Total;

LOAD * Inline

[

Location,Date,Revenue

A, Jan-13, $1000

A, Feb-13, $1100

A, Mar-13, $1200

A, Apr-13, $1300

B, Jan-13, $1400

B, Feb-13, $1500

B, Mar-13, $1600

B, Apr-13, $1700

C, Jan-13, $1800

C, Feb-13, $1900

C, Mar-13, $2000

C, Apr-13, $2100

];

New:

LOAD

Location,Date,Revenue,Running_Total

Resident tmp

Order By Location, Date;

DROP Table tmp;

MarcoWedel

Hi,

or given a table

tab1:

LOAD Location,

     Date#(Date, 'MMM-YY') as Date,

     Num#(Revenue, '$ 0,000') as Revenue

FROM [http://community.qlik.com/thread/121052] (html, codepage is 1252, embedded labels, table is @1);

by joining the Running Total column like:

Left Join (tab1)

LOAD Location,

     Date,

     Num(If(Location=Peek(Location), Revenue+Peek([Running Total]), Revenue), '$ 0,000') as [Running Total]

Resident tab1

Order By Location, Date;

QlikCommunity_Thread_121052_Pic1.JPG.jpg

hope this helps

regards

Marco

MarcoWedel

or at front end

=Num(RangeSum(Above(Revenue,0,RowNo())), '$ 0,000')

QlikCommunity_Thread_121052_Pic2.JPG.jpg

regards

Marco

Not applicable
Author

Manish - your solution works fine for the same data set I published.  Unfortunately, I could not get it to work on the actual data set.  Attached is an actual set of my data.  I think the dates are the problem.  My data set has dates in the MM/01/YYYY format not the MMM-YY like in the example.  Each record is a sum of revenues for the month so the data is represented as the first day of each month (02/01/2014, 03/01/2014 for example).

Here is my script:

RuningTotalTemp:

LOAD Location,

     Date,

     Revenue

FROM

XXXXXX

(ooxml, embedded labels, table is Sheet1)

;

RunningTotal:

Load

  Location,

  Date,

  Revenue,

  If (Location = Previous(Location), RangeSum(Revenue + Peek('Running Total')), Revenue) as RunningTotal

Resident RuningTotalTemp

Order by Location, Date

;

Drop Table RuningTotalTemp

;

Anonymous
Not applicable
Author

Hello Marco:

I think your last post on this topic is close to what I want but I am having trouble still.  I have a simple accumulation problem in calculating a balance per the following Excel Input:

Inv#TransDateTransTypeTransAmt
16/1/2014Inv10
26/20/2014Inv22
36/25/2014Inv77
16/10/2014Pymt-3
16/15/2014Pymt-7
27/1/2014Pymt-8
37/10/2014Pymt-7

So I would like to create a Straight Table showing the calculated balance per invoice.

Inv#     TransDate     TransType     TransAmt     Balance

1          6/1/2014       Inv                   10                10

1          6/10/2014     Pymt                 -3                 7

1          6/15/2014     Pymt                 -7                 0

2          6/20/2014     Inv                    22                22

2          7/1/2014       Pymt                 -8               14

In your last post, you have the expression:  =Num(RangeSum(Above(Revenue,0,RowNo())), '$ 0,000')


Because the example given in the original post uses an "In-Line Table", the 'Above' term in your post creates an error in my script.  Can you redo your example to show the syntax for the Excel File content I describe.


Thanks

David

MarcoWedel

I tried to change the example to a Excel data source with the field names and data you provided:

QlikCommunity_Thread_121052_Pic3.JPG.jpg

The balance expression now is defined as:

=RangeSum(Above(TransAmt,0,RowNo()))

hope this helps

regards

Marco

MK_QSL
MVP
MVP

Temp:

Load

  Location,

  Date#(Date,'DD/MM/YYYY') as Date,               // JUST ONE CHANGE

  Revenue

Inline

[

  Location, Date, Revenue

  A, Jan-13,   1000

  A, Feb-13,    1100

  A, Mar-13,    1200

  A, Apr-13,    1300

  B, Jan-13,    1400

  B, Feb-13,    1500

  B, Mar-13,    1600

  B, Apr-13,    1700

  C, Jan-13,    1800

  C, Feb-13,    1900

  C, Mar-13,   2000

  C, Apr-13,    2100

];

Final:

Load

  Location,

  Date,

  Revenue,

  If(Location = Previous(Location), RangeSum(Revenue + Peek('Running Total')), Revenue) as [Running Total]

Resident Temp

Order By Location, Date;

Drop Table Temp;

Not applicable
Author

This did not work with my attached Excel data set.

Here are the results:

Running Total Results.jpg