Running Total in Qlikview

[#1]

My table has the stock movement for each item by day. So at any point if I want to find the current stock, I need to sum all the stock movements from the day business started.

Now I want to create a report which shows the stock level over a period of time. The period can be selected by the user.

Here is a simplified version of my table:

Stock:

LOAD * INLINE

[Brand,Item,Reason,Date,StockMovement

EveLom,E001,OpeningStock,01/01/2014,200

EveLom,E001,Sale,05/01/2014,-100

EveLom,E001,Purchase,01/03/2014,100

EveLom,E001,Sale,01/05/2014,-50

EveLom,E001,Sale,01/10/2014,-50

EveLom,E001,Sale,21/10/2014,-20

EveLom,E001,Sale,01/01/2015,-30

EveLom,E001,Sale,01/03/2015,-10

EveLom,E001,Purchase,01/04/2014,100

EveLom,E002,OpeningStock,01/01/2014,2000

EveLom,E002,Sale,05/01/2014,-100

EveLom,E002,Purchase,01/03/2014,100

EveLom,E002,Sale,01/05/2014,-50

EveLom,E002,Sale,01/10/2014,-50

EveLom,E002,Sale,01/11/2014,-20

EveLom,E002,Sale,01/01/2015,-30

EveLom,E002,Sale,01/02/2015,-10

EveLom,E002,Purchase,01/03/2014,100

LQ,L001,OpeningStock,01/01/2014,1000

LQ,L001,Sale,05/01/2014,-100

LQ,L001,Purchase,01/03/2014,100

LQ,L001,Sale,01/05/2014,-50

LQ,L001,Sale,01/10/2014,-50

LQ,L001,Sale,21/10/2014,-20

LQ,L001,Sale,01/01/2015,-30

LQ,L001,Sale,01/03/2015,-10

LQ,L001,Purchase,01/04/2014,100

LQ,L002,OpeningStock,01/01/2014,5000

LQ,L002,Sale,05/01/2014,-100

LQ,L002,Purchase,01/03/2014,100

LQ,L002,Sale,01/05/2014,-500

LQ,L002,Sale,01/10/2014,-500

LQ,L002,Sale,01/11/2014,-200

LQ,L002,Sale,01/01/2015,-300

LQ,L002,Sale,01/02/2015,-100

LQ,L002,Purchase,01/03/2014,100

];

There four items and they are grouped under 2 brands.

Now I want a stacked bar chart for Stock Level by Brand Vs Month.

The chart should look like below. (I created this one for sales.)

Months will be in X-Axis.

Stock will be in Y-Axis.

I have attached a qvw document with the sample data and calendar.

I tried several methods without any success. Any ideas/guidance will be much appreciated.

[oknotsen]

added this column to the table you used in your upload. I think it achieves what you want, but I am not sure. Please check:

= RangeSum(Above(sum(StockMovement), 0, RowNo()))

I can't get your chart to work yet, but I think these are at least the numbers you wanted, right?

[rubenmarin · Accepted solution]

Hi Mayuran, please check attachment, I added a YearMonth field and I didn't worry about design, only to get the values.

Hope this helps you!

[ramoncova06]

Here is one without modifying the backend script

[#5]

Thanks for the prompt response.

This works but if you select year 2015, the values are not correct as the stock should be calculated from 2014 Jan.

[#6]

Thanks for the prompt response.

This just sums up the values for the current year and not shows the running total.

[#7]

Thank you for the answer. This works      

Ideally I am looking for an answer that does not touch the backend script. If I cannot find any I will use this solution.

Once again thanks.

[ramoncova06]

here is with using the full accumulation

[jonathandienst]

See this blog post for some information on accumulation:

Accumulative Sums

[rubenmarin]

It will be hard without a 'Date' or 'YearMonth' field, for complete years can be done but if the user selects 2nd semester of 2014 an first semester of 2015 I don't see a way to use set analisys without a field that combines month and year in any way.