Stock Calculation

[#1]

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.

[Nicole-Smith]

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):

[spividori]

Hi.

Check out this discussion, maybe this can help you.

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

Regards.

[#4]

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.

[#5]

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