quantity on hand

[CRdesigner]

hi, good day to all!

i need your help guys about inventory regarding quantity on hand.
this is the scenario:

transaction date: quantity
jan 1,2009 +1000
jan 2, 2009 +1000


for example i have a beginning balance of 1000 pcs of item1 as of january 1,2009 which will be recorded in quantity on hand when i process a "day end." And then i ordered from my supplier another 1000 pcs of item 1 in january 2,2009.

so my quantity on hand will be 2000 pcs per item1.

what if i want to check my balance during january 1,2009. i want to see it's original value as 1000. but when i do enter january 1,2009 in transaction date, i get 2000 pcs not 1000 pcs.
result:

transaction date: quantity on hand
(parameter)
jan 1,2009 2000
jan 2, 2009 2000

i want to see the value of 1000 in january 1,2009 ( is this possible)?

i will be waiting for you reply

thanks and best regards!

 

[Jay Converse]

Use ICHIST, and start from the top. Never clear history.

 

[CRdesigner]

Hi tuba2007,

thanks for the reply, i tried to include the ICHIST table in my report and this is the output of it:

item quantity transdate

item1 8 1/1/2001
item1 87 1/1/2001
item1 -8 1/1/2001
sum: 87
item1 5 1/2/2001
item1 15 1/2/2001
item1 -15 1/2/2001
sum: 5

total: 87+5 = 92

is the quantity here is the quanity on hand? my analization here is: item1 has a total quantity of 87 on 1/1/2001 and the total quantity for that item1 as of date(1/2/2001) would be 92.

but what if i only chose 1/2/2001? will i get the quantity of 92? or just 5? is it possible that i get the quantity of 92 when i enter 1/2/2001. what would be the formula for that?

 

[Jay Converse]

Use running totals.

 

[CRdesigner]

hi,

before you answer, i did some changes in the report ^_^

and this what happen

lets say same scenario

i got a total of 92 per item1 in 1/2/2001 right?

so what i did is make a running total not a formula rtotal1.

in this rtotal1: summarize quantity, evaluate each record, reset at item no.

so what happens next is it sum up every quantity every period.
ehehe it works.
i will just show the total for easy comprehension:

group#1 : item 1
jan 1 : 98 rtotal1: 98
jan 2: 100 rtotal1: 198
Jan 3: 100 rtotal1: 298
jan 4: -50 rtotal1: 248
jan 5: 5 rtotal1: 253
jan 30: 1000 rtotal1: 1253


thank you very much tuba2007!

 

[CRdesigner]

hi again,

there's a problem again,

lets say i have total quantity of 5000 per item A as of january 31,2009.
but when i try to filter the date of january 15, 2009 it only shows the total quantity of it,example 1500 quantity of item A.

but i want to get the total quantity of item A from january 1-15 and not only the total quantity of january 15.

how could i get it? what formula?

i will be waiting for some reply guys, please help!

thanks and best regards,

 

[Jay Converse]

Don't filter in the record selection, use conditional formatting to not display what you don't want.