How to build an efficient dataflow to transform YTD values in Monthly values ?
Hi Board experts,
Everything is in the title, we are working on a computation engine that works both with monthly and Year-to-date values.
In a dataflow we currently do the operation to convert a monthly cube in a YTD value cube, but what would be the most efficient way to do the reverse ? How would you set it up to obtain an HBMP algorithm ?
Thanks for your help,
Etienne
Answers
-
Hi Etienne CAUSSE,
Maybe I’m missing something, but would a dataflow like one of these meet your expectations?
a. Value, with Cumulative Value function applied and cycle of 1000
b. Value, with Cumulative Value function applies and cycle of 1000 and offset of 1
c. Algorithm c=a-b
Since these are already YTD values, using just an offset variance may do the trick too.
a. Value
b. Value, offset 1
c. Algorithm c=a-b
3 -
Hi Bob Gill ,
Did not have time to test yet, but from what I remember of the documentation, this would probably not run as HBMP ?
Also I'm curious, why the cycle of 1000 ?
0 -
Hello Etienne,
To go from YCV to Monthly value in HBMP you need to subtract the YCV cube to itself with a -1 offset.
You will get an exception on the first period of the year in which you will need to put the value of the YCV cube.
Please find attached a small example.
To run YCV in HBMP it's more tricky if you also need that you might create an independent entity MonthNUmber with members from 1 to 12 and map the months to this entity,
So that January will go in all the members from 1 to 12 february in all the members from 2 to 12 until december on 12.
Then aggregate by month and remap 1 to 12 one by one to the months and you will get the YCV in 5 or 6 steps.
I didn't prepare an example for this but if you need i can prepare it
Antonio
7 -
Hi Etienne,
the typical way I use is this:
a: CubeYTD
b: CubeYTD prev Month
c: Month Flag (= 1 if January)
d: CubeMonth = if(c=1,a,a-b)
But this don't run in HBMP mode.
Its efficiency can be increased narrowing the selection as much as possible.
This is a case: selection is narrowed to closed months of current year (dynamic, through a cube by month).
I add some details about structure and entity items nr, that impact on df execution time.
Even if it's a cellbased, it's immediate.
Anyway, in this period I'm optimizing my processes moving to HBMP data flows with great results and, thanks to your hint, I'm interested to find out a way to do this calculation aswell.
I would have some more details about:
cube structure: nr of dimensions, sparse or dense, items number
selection range of the process: e.g. last month only ?
2 -
If you use cycle of some large enough number to get to the beginning of time in your linear time dimension, it gives you a life-to-date sum, rather than just one YTD. This overcomes the error when calculating activity from YTD in the first period of the year. It probably seems like an error to recalculate a life to date number of a YTD cube because the life-to-date sum is irrelevant. It’s only useful for calculating the variance to the same thing in the prior period. The algebra will give you the right activity in any period.
2 -
Thanks Antonio Speca I'll have a look at your example and run some tests.
1 -
Clear Bob Gill, I'll remember the trick
1
