Showing latest inventory levels as a measure

[jinashree]

How do you correctly show balances?
Hi there. My cube is at the daily level. I have sales and inventory numbers - on Monday I have 100 pieces of inventory and 200 sales. On Tuesday I sell another 50 pieces so inventory is now 50 and sales is 250. If I drill up to the week on Friday, the cube correctly shows sales as 250. How do I get the cube to only show 50 for inventory? 'Last' on the date doesn't seem to work - since nothing happened in inventory after Wednesday, they have 0 as the value in the inventory level column.
I am getting 0s for the rest of the week - so the last value shows as 0. It needs to show the last activity - i.e. 50, NOT the sum of 100+50, for that week and not 0s! HELP!

 

[drlex]

jinahree,
The only way I've been able to manage this is by:
1) two queries, one for sales and one for stock,
2) setting cumulative special categories in the date dimension.
3) including an initial date for the inventory figures which is part of each cumulative category
4) using a calculated measure of current inventory = initial inventory - sale
5) excluding the initial inventory measure from the cube.

This way can be a major headache for maintenance for either manual changes or scripting (fortunately I only have to maintain the model as cumulative by month). I do wish that Cognos would include some cumulative by date functionality to avoid such kludgy work-arounds.

soi la, soi carré

 

[yksvaan]

Last period will work, but in the Time-tab on the levels of the time dimension you must uncheck "Generate all categories in the period" (and clear your present time categories you might have in the model). Then you won't have the Friday in your cube at all if you don't have any data for it. And you must ensure, that you bring in the datasource the inventory level for each day you have sales, or any other measure's values. So to speak, your time categories must be build based on the inventory level dates.

Or, make a separate query for inventory balance, where you show inventory level for each day in the calendar, no matter whether you have sales or not.

Cheers


[blue]Backup system is as good as the latest recovery[/blue]

 

[jinashree]

Thanks for your suggestions. I will try them and let you know if it works.