I want to extract the end of month value of an inventory cube.
Day by day, I have the cumulated value but I need a end of month value for the accountancy.
Thanks a lot,
Hi Sébastien LEGER I had a similar requirement recently - I had daily closing daily stock balances, but just needed the closing balance for the week. What I did was created a settings cube by day to hold a flag against the last day of the week, then I was able to multiply the flag by the daily stock position to get a cube with just the end of week balance. There are no doubt different ways to achieve the same result, but this one worked for me.
I tried this method and it works.
Thanks a lot Phil.
Great, happy to help.
Hi Sébastien LEGER, just alternate method that doesn't require a flag cube, it makes use of previous period and an algorithm in the layout instead.
In a layout add the cube with your values and check 'previous period', display with 'Day' by row. The value now showing against the 1st of each month will be your closing value from the previous month.
Then add a new 'entity' block to the layout and set this to the 'SAY' entity.
Then add an algorithm block to split out the day part of the DAY entity block (eg. if the entity is block B then depending on date format you may be able to simply use Left(B,2) as your algorithm).
You can then filter your layout on the algorithm block (ie >0.5, < 1.5), which with the previous value flagged will give you the value of the day before 1st of the month, which will be your closing value.
Retrieving data ...