topic Re: Stock Calculation in QlikView

Stock Calculation

Tue, 19 Nov 2013 05:33:33 GMT

hi,

I need to calculate current stock.

As per calculation , for each date, the current stock is sum of all inventory previous to that date.

Now i want to show monthly stock in a bar chart.

For 2013 Jan , the stock would be sum of stock quantity previous to 2013-Jan-31

For 2013 Feb , the stock would be sum of stock quantity previous to 2013-Feb-28

for example, in this table

LOAD * INLINE [

Year, Month, Day, Quantity

2012, 12, 1, 1

2012, 12, 12, 2

2012, 12, 18, 2

2012, 12, 31, -4

2013, 1, 1, 1

2013, 1, 12, 2

2013, 1, 18, 2

2013, 1, 31, 4

2013, 2, 1, 5,

2013, 2, 12, 4,

2013, 2, 18, 1,

2013, 2, 31, 1

2013, 3, 1, 2

2013, 3, 12, 2

2013, 3, 18, 2

2013, 3, 31, 2

];

Stock for 2013 Jan=1+2+2-4 +1+2+2+4

Stock for 2013 Feb=1+2+2-4 +1+2+2+4 +5+4+1+1

We can calculate individual using set analysis, but how to calculate all at same time for bar chart.

How can i create monthly stock bar chart....

Please suggest.

Re: Stock Calculation

Nicole-Smith — Tue, 19 Nov 2013 17:06:10 GMT

I believe what you want is Full Accumulation (and you will want to make your dimension a date, as right now you only have it as a month):

Re: Stock Calculation

spividori — Tue, 19 Nov 2013 19:10:00 GMT

Hi.

Check out this discussion, maybe this can help you.

http://community.qlik.com/message/423634?et=watches.email.thread#423634

Regards.

Re: Stock Calculation

Wed, 20 Nov 2013 06:51:10 GMT

I cannot make dimension a date, i have over 10 years of data (2003-2013), now if user click on 2013, full accumulation will accumulate only 2013 data.

Re: Stock Calculation

Fri, 22 Nov 2013 16:22:59 GMT

Arun,

The solution for your problem can be solved by a so called AsOfDate table.

I faced the same problem and was able to solve it.

The AsOf needs changes in the data model to create the AsOf table and fill it with the necessary records. The advantage is that it likely performs better on large data sets. It's also quite versatile since you can add several sets of records to meet different scenario's. The expressions you end up with in the charts are also less complicated. That said, it will likely take you some time to fully understand the AsOf table concept and realize all the places where you can put it to good use.

The above info is taken from this document: http://community.qlik.com/docs/DOC-4252

There the concept of an AsOfTable is explained. But in stead of an rolling period you need to built up your table form the first transaction date.

An example how to build the table can be found here: http://community.qlik.com/message/93904#93904

You should look at the helpful answer of John Witherspoon

I hope the concept of AsOfDate will become clear to you.

If you struggle let me know I I will then prepare an example .qvw with an extract of my dataset.

All credits for this solution go to John Witherspoon and Gysbert Wassenaar.

Regards,

Gerrit