Inventory units for each week

[#1]

Dear all

I have a table with 30 million transactions( in/out from stock).

It is easy to calculate a rolling balance (takes 2-3 minutes), but what i need, is the inventorylevel week by week (end of week) until today.

Until now, I have tried to insert the dates for the week end using while and intervalmatch. But with 2,350,000 various combinations, there will be quite many rows.

My final thought was, maybe to do something with set analysis, but still no success.

Do any of you have any great ideas on , how to achieve the above requirement? I have attached an example of what the data looks like

Thank you in advance for any help.

/Martin

[#2]

You can probably use the WeekEnd and WeekStart statements for this. Will be easier on your system to add those to each record than to use IntervalMatch.

[Oleg_Troyansky]

Mark,

calculating the balances from the transactions is quite a chore... Here is the general idea:

1. Generate distinct lists of all Items and all Weeks in your range

2. Join between the two, to generate all possible permutations of Items and Weeks

3. Left Join into this table summary of transactions, aggregated by Item and Week.

4. Sorting the table by Item and Transaction Date, reload the table, calculating the running total :

if(previous(Item) = Item, peek(RunningTotal) + Transaction, Transaction) as RunningTotal

Assuming that you have ALL the transactions from each Item (from the beginning of the Item's life cycle), you should be able to calculate all weekly totals for all Items.

[#4]

Hi Oleg, thank you for your reply,

As i wrote in my post, its is easy to create the balance of the items (se my attach file in previous post).

What tricks me is the amount of data (30 mio rows, with 2,3 mio items). If you add that up it is quite many rows. What i basically need, is some thoughts on how to add the weeks without any transactions, in the best way.

I hav tried to do a while (time is an issue here), Intervalmatch (performance is an issue on this one).

/Martin

[#5]

Hi Martin ,

"------- some thoughts on how to add the weeks without any transactions, in the best way . ------"

Since your data size is huge, I think addition of Master Calender will work for you .

You can copy Master calender (refered code from some other links) from link : http://community.qlik.com/forums/t/26678.aspx & modified to accomodate your dates .

Then you can link your Transaction Dates with Dates in Master Calender & pull report based on Weeks mentioned in Master calender .

I think that will solve your query .

Let me know in case of any dobuts.

Cheers ,

Bhushan N

[#6]

Hi Bhusnan

It will not solve my issue. That script will just add a calendar.

Let take an example..the last transaction on a specific item is january 5, but we still have 10 units on stock, then i wont get the actual value as of today, when adding a calendar.

/Martin

[#7]

Hi Martin ,

Check this link : http://community.qlik.com/forums/t/26601.aspx

Cheers,

Bhushan N

[#8]

Nice technique with the application Martin, thansk for your help.