Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Formula for finding transactions in trailing months

Status
Not open for further replies.

travelerII

Technical User
Jun 4, 2009
66
US
I am using Crystal Reports 10.
I am trying to reconstruct our end of month inventory going back in time from current inventory.
I take the current inventory and add the net transactions, basically receipts and issues, for a given time period, in this case I take inventory for today, 1/10/12 for an item and add the net transactions for the month to date and I get the inventory on 12/31/11. The following is the formula I am using to get the net transactions for the current month however my sum function adds the inventory item above it to the next one and so it is cumulative instead of just the net transactions for that item.
if {STOJOU.CREDAT_0} = MonthToDate then Sum ({STOJOU.QTYSTU_0},{STOJOU.ITMREF_0} )
Then I would need to go back for each proceeding month and get the ending inventory for those months on a rolling 12 month basis. This is where I run into difficulty.(Aside from the summing problem)
I am using the create date field {STOJOU.CREDAT_0} for the transactions and as I said month to date works to give me Dec ending inventory and then last full month works to give me Nov ending inventory but how to go back further is where I am stuck. I have tried (last full month - 1) and the dateadd function without success. I thought the dateadd function would work but in won't accept currentdate as the startDateTime.
Any thoughts on how to tackle this are apprectiated.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top