Live Connection SAP BW - Extrapolation of Budget values to a monthly split (linear)
Hi there,
I would like to extrapolate the following budget values to a monthly view from a given year view only. For example, from SAP BW we only get the annual accumulated budget figure. I would like to add the remaining months manually into Pyramid and divide the yearly figure by 12 to get a monthly view for selective. Similarly for accumulative, I would like to divide the yearly figure by 12 and multiply by the month number (linearly) but show the monthly figures in an accumulated state.
Please refer to the picture (as-is) which depicts the view now, and (to-be) which shows how I would like to do and see it in Pyramid. Thank you very much.
1 reply
-
Hi Tobias,
Apologies for the slow response!
I don't have a SAP BW cube with appropriate data, so I've used an Analysis Service cube, which uses the same MDX calculation language as SAP BW, so it should apply the same.
I've taken my Sales for December 2016 as your Budget value (which occurs in December it seems) to calculate the budget spread, i.e. that value /12. Then a calculation that depends on the outer dimension to determine whether to calculate the cumulative budget through the year, or allocate the same budget to each month. I've used countries Australia and Canada to represent Cumulative and Selective.
So first we create the Budget, i,e, the value from December for my year in question:
Then use that budget to create an allocated budget measure with a cumulative total if the country is Australia (Cumulative) or just the budget for each month if the country is Canada (Selective):
Putting the allocated budget on my grid with my countries gives the result you are looking for:
Hope that helps.
Ian