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

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

Fri, 21 Aug 2009 01:28:18 GMT

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

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

Fri, 21 Aug 2009 12:11:50 GMT

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

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

Mon, 24 Aug 2009 16:23:53 GMT

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

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

iuliancres — Wed, 17 Feb 2010 15:10:49 GMT

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

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

Wed, 17 Feb 2010 15:15:52 GMT

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!