Stock problem

[#1]

I have a problem finding number of opening and closing stock within a month. Simplified example of data:

ID, StartMonthYear, ClosedMonthYear

a, 01-2010

b, 01-2010, 02-2010

c, 02-2010,

d, 02-2010,

e, 02-2010, 04-2010

f, 04-2010, 04-2010

What I want is the following result (StartMonthYear is dimension):

StartMonthYear No of IDs No of Closed ID Stock Accum. Stock

01/2010 2 0 2 0

02/2010 3 1 2 4

04/2010 1 2 -1 3

So I need to find how many ID has been started in e.g. 02-2010 and how many has been closed in that same month regardless of their StartMonthYear, which then gives me the Stock.

Any ideas ?

[#2]

Hi

can you try for No of Closed

sum(if(ClosedMonthYear=StartMonthYear,1,0))

Regards

Juerg

[#3]

Thanks for the suggestion Juerg. I have already tried that but that will only give me number of closed that has started the same month as they were closed, so unfortunately I can't do it like that.

[johnw · Accepted solution]

Maybe this?

[Stock Events]:
LOAD
StartMonthYear as AsOfMonthYear
,'Start' as Event
,ID
RESIDENT [Stock]
;
CONCATENATE ([Stock Events])
LOAD
ClosedMonthYear as AsOfMonthYear
,'Close' as Event
,ID
RESIDENT [Stock]
;

Dimension = AsOfMonthyear
Start IDs = count({<Event={'Start'}>} ID)
Close IDs = count({<Event={'Close'}>} ID)
Stock = "Start IDs"-"Close IDs"
Accum = rangesum(Stock,above(Accum))

I'd probably be tempted to use intervalmatch to directly associate each ID with each month it was in stock, and just count(ID) to get my current stock. I don't like the idea of the chart having to use above() to get the data. Just makes me nervous. I like data solutions more than chart solutions.

[#5]

Thanks a mill John ! Works like a charm. If I find the time I might give interval match a go, but for the time being I'm happy.

[#6]

Hi Guys,

I did it like this:

StockTemp1:
load:
StartMonthYear as StockMonthYear
resident Stock
outer join (StockTemp1)
load
ID
, StartMonthYear
, ClosedMonthYear
Resident Stock;

StockTemp2:
Load
StockMonthYear
, if(StartMonthYear< StockMonthYear and ClosedMonthYear> StockMonthYear ,1,0) as #Stock
, if(StartMonthYear= StockMonthYear,1,0) as #Started
, if(ClosedMonthYear= StockMonthYear,1,0) as #Closed
Resident StockTemp1;

Stock:
StockMonthYear
,Sum(#Stock) as #Stock
,Sum(#Started) as #Started
,sum(#Closed) as #Closed
Resident StockTemp2
Group by StockMonthYear;
Drop tables StockTemp2, StockTemp1;

[#7]

sorry,

if(StartMonthYear< StockMonthYear and ClosedMonthYear> StockMonthYear ,1,0) as #Stock

must be: if(StartMonthYear< StockMonthYear and (ClosedMonthYear> StockMonthYear or isnull(ClosedMonthYear)),1,0) as #Stock

[suniljain]

Pls try attached qvw file.