INVENTORY RUNNING BALANCES (INITIAL/ENDING) - multiple period selection

[#1]

Hi everybody.

I have to prepare lot of reports for Warehouse movements and Accounting (Debits/Credits). All these reports need initial/ending balances for the selection : Daily/Weekly/Monthly etc.

I get the transactions in a table, I use aggregate functions per day/product and I get the movements. I use "Autogenerate" to create all Year dates, so my balances to not have gaps if there are no transactions for a given day. I take the initial balances from the ending balance of the previous day with the " above() " function. Then I export the calculated table and reload it. I read in the forum that the same could be done with resident table in the phase of loading but I couldn't find good example.

Anyway, In the attached example the data is loaded with the balances already calculated. What I needed to do is to prepare Weekly/Monthly reports from the daily balances. I've got it using the "WeekStart" and "Monthstart" functions. Though, my application is not working if I select more than one Week/Month. Can you please assist? If anybody wants to provide sample application I would be grateful.

Thanks in advance,

Mangas

[#2]

I suppose I need to get the Initial Balance for the "Date=Min(Date)" where "Min(Date)" is the smaller date in the current selection. I tried with Min(Date) but while it returns the correct results when used as separate expression when trying to incorporate it in SUMIF condition it fails. (Sum (IF (Date=Min(Date), [Initial Balance], 0))

Regards,

Mangas

[#3]

So my dearest frineds, I foubnd the sollution and I would like to share with you:

When selecting more than one week in order to get the INITIAL_BALANCE only for the first day of teh SELECTION I use condition as follows:

Sum( If( Min(TOTAL Date)=Date,INITIAL_BALANCE, 0) )

Cheers,

Mangas

[iuliancres]

Thanks a lot, your post helped me on a similar issue

[#5]

This is nice. I'm glad to hear that.

Could you please share the way you CALCULATE the balances (daily, weekly or monthly?).

Generally I think there are two ways:

COMPLICATED ONE:

1.1) Load MM data (or accounting whatsoever)

1.2) Make flat table with all transactions, dimension the TIME you wish (lets say week)

1.3) Export the already calculated table

1.4) Reload data again, taking as new input the calculated table

SIMPLE ONE:

Making use of functions PEEK during the load and pre-calculate everything on the fly.

I use the fisrt method ;-), but I'd like to see a file with the second method implemented. If you have something, please share!