Hello,
Maybe you guys can help me with this... it has been bugging me for days!
Info:
Product A has 3 stock units updates (Jan - 600 units, Sep - 500 units and Oct - 1000 units). In some analysis, I want to know the stock units for a selected calendar date. For each product I have a maximum of one entry per month. In case I have no register for a product in a month I should consider the previous stock value for that product.
Code used:
sum ({<Analysis={"Stock"},Year=,Month=>}
if( aggr(max( {<Dat={"<= $(vMaxDate)"},Analysis={"Stock"},Year=,Month=>} DateStockNum), CodProdut )=DateStockNum
,
Units ))
(vMaxDate returns the maximum of the selected calendar date)
Example: If I select January I should get 600 units, if I select any month from February to August I should get 600 units, if I select September I want to get 500 units, in October 1000 units and in November 1000 units.
The problem: In the months from September on, I get 0 (can't find any units).
I tried this for debugging:
concat ({<Analysis={"Stock"},Year=,Month=>}
if( aggr(max( {<Dat={"<= $(vMaxData)"},Analysis={"Stock"},Year=,Month=>} DateStockNum), CodProdut )=DateStockNum
,
' Match:'& Month(date(DateStockNum)) & Month(aggr(....)),
' NotMatch:'& Month(dateDateStockNum )& Month(aggr(....))
)
It returns :
Selecting Jan - Aug-> Match:Jan Jan NotMatch:Sep NotMatch:Oct
Selecting Sep ->NotMatch:Jan Sep NotMatch:Sep NotMatch:Oct
Selecting Oct ->NotMatch:Jan Oct NotMatch:Sep NotMatch:Oct
It is only comparing with the first DateStockNum of the group of DateStockNum. Can anyone explain this behaviour and/or knows a work around?
Any comments will be deeply appreciated!
Thank you!
