topic Re: Running Total in Qlikview in QlikView

Running Total in Qlikview

Fri, 20 Nov 2020 18:02:30 GMT

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.

Re: Running Total in Qlikview

oknotsen — Mon, 20 Apr 2015 17:23:33 GMT

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?

Re: Running Total in Qlikview

rubenmarin — Mon, 20 Apr 2015 17:48:11 GMT

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!

Re: Running Total in Qlikview

ramoncova06 — Mon, 20 Apr 2015 18:00:36 GMT

Here is one without modifying the backend script

Re: Running Total in Qlikview

Mon, 20 Apr 2015 22:48:59 GMT

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.

Re: Running Total in Qlikview

Mon, 20 Apr 2015 22:50:38 GMT

Thanks for the prompt response.

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

Re: Running Total in Qlikview

Mon, 20 Apr 2015 22:54:01 GMT

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.

Re: Running Total in Qlikview

ramoncova06 — Tue, 21 Apr 2015 02:22:32 GMT

here is with using the full accumulation

Re: Running Total in Qlikview

jonathandienst — Tue, 21 Apr 2015 06:11:47 GMT

See this blog post for some information on accumulation:

Accumulative Sums

Re: Running Total in Qlikview

rubenmarin — Tue, 21 Apr 2015 08:20:23 GMT

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.

Re: Running Total in Qlikview

Tue, 21 Apr 2015 11:06:33 GMT

This prints correct values but when I select Year 2015, it still shows the values for 2014 as well.

When Year 2015 is selected, only months from 2015 should be shown.

Re: Running Total in Qlikview

ramoncova06 — Tue, 21 Apr 2015 14:58:47 GMT

I am not sure on how to do that without modifying the backend script, I would suggest using Ruben solution instead

Re: Running Total in Qlikview

Tue, 21 Apr 2015 15:14:12 GMT

Thanks. This is the best solution I have come across. I marked yours as the correct answer.