Latest Value Per Month
I have a cube by Employee, Cost Center and Day. I would like to populate a cube which is by Employee, Cost Center and Month with the latest value (as close to the last day of the month as possible) for each Employee, Cost Center combination as shown by screenshot. Doing an extract reload unfortunately does not work as there is too many rows when all employees are selected (I have tried to narrow the selection before the extract reload too). How would I achieve this using dataflows? I have played around quite a bit with "last value" time function but could not get it to work.
Answers
-
Hi Yas,
Thanks for posting the question. There would probably be a few different ways to do this. One way I can think of would be the following: 1) Apply your main time range selection (the months you need to run this process for). 2) Have an integer temp cube structured by day and populate it with a dataflow a=1. Another int temp cube is structured by month and set it to a=b (where b is the day cube populated before). This will flow the number of days in each month into the second temp cube. 3) Have a data flow step with a layout where you drag your month integer temp cubes from above (block "a"), and the day entity (block "b"). In the expression, put the following algorithm to populate your temp 1 cube you had created in step 1: IF(DAY(b)=a, 1, 0). This will basically flag the last day of each month in a temp cube by day for you. Then you can use this temp cube in a Select Entity Based on Cube step to select only those flagged days before your main dataflow, so you only flow the data from the days you care about into your month cube.
Kind regards,
Hamza
1 -
Hi Yas,
There are two last value functions, one is analytics and the other is the time function. The time function should perform the purpose you're trying to achieve, if you're not concerned about seeing 0s as a last value. Board doesn't recognise zero as a value at the lowest level so Hamza's solution may work for this case.
Thanks,
Scott
2
