Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
CHL
Contributor
Contributor

Inventory at Point in Time

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

5 Replies
qumniusomnius
Contributor III
Contributor III

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)

sridhar240784
Creator III
Creator III

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

-Sridhar

CHL
Contributor
Contributor
Author

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

avinashelite

share your app with the sample data, that could useful

CHL
Contributor
Contributor
Author

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