topic Re: Inventory at Point in Time in QlikView

Inventory at Point in Time

CHL — Wed, 29 Jul 2015 08:58:44 GMT

Hi All,

I'm attempting to work something out but cant for the life of me get my head round how to do it (maybe not enough coffee!).

I have 2 tables:

Inventory (this shows whats in stock right now).

Inventory Transaction (this is every transaction for the past year so stock in and stock out). Including a datetime stamp of the transaction.

What I need to do is to be able to take a point in time and see what was in stock then.

Stock in minus stock out will only give me 'net stock' for that day. I obviously need to add this to all previous days to get total stock.

Any ideas? Is it even possible?!

Thanks,

Chris

Re: Inventory at Point in Time

qumniusomnius — Wed, 29 Jul 2015 09:08:42 GMT

you need to add up current value with all the changes from the date you are interested in untill today.

it should look something like this:

sum({<PostingDate ={'>= InterestDate'}>} ChangesInStock)

+sum(CurrentStock)

Re: Inventory at Point in Time

sridhar240784 — Wed, 29 Jul 2015 09:44:28 GMT

it would help ppl to respond your thread if you Could post a sample data and expected result.

-Sridhar

Re: Inventory at Point in Time

CHL — Wed, 29 Jul 2015 09:46:53 GMT

Hi Borja.

Thanks for your reply.

Is PostingDate the date of the inventory transaction and InterestDate the date I want to know stock levels?

If so then will that formula only work for yesterdays stock?

If my current level is 500 and i select 01/01/2015 and work out changes and on that day I had -15. Then it will take 15 from 500 leaving me with 485 and not take into account anything in between?

Chris

Re: Inventory at Point in Time

avinashelite — Wed, 29 Jul 2015 10:08:41 GMT

share your app with the sample data, that could useful

Re: Inventory at Point in Time

CHL — Wed, 29 Jul 2015 10:13:43 GMT

Hi All,

Attached is a sample QVD for InventoryTransaction.

Inventory is just a number we will call it 4000.This is total items in stock as of now.

Thanks

Chris