YTD allocation in a periodic application

[Former Member]

Hi Experts,

I have a periodic application, FINANCE, wherein I need to do allocation based on YTD values. I have written a logic to first calculate the YTD value of the month the user selected and also the YTD value of its prior month and post them into two seprate dummy time. I need the YTD value of the prior month so that after the allocation I could subtract that in order to get again the periodic value for that month. So my logic flows as follows:

1. Calculate the YTD of the selected time and post it on a dummy time.

2. Calculate the YTD value of the prior time and post it on a another dummy time.

3. Use the first YTD value (selected month) for the allocation.

4. Subtract the second YTD value (prior month) from the allocated amounts.

All seems to be well but my problem is that I'm almost tripling my records in this proceeds and I really have huge number of data for allocation. Is there a way to simplify this like is there a BPC function I can use so that I don't need to go to this whole process?

Thanks,

Marvin

[Former Member · Accepted solution]

Hi,

You have three options:

1) Use Business transformation rule to calculate and post YTD values in a separate time member. This is similiar to what you have done, but you dont have to write your own script logic to calculate YTD.

2) Use MDX to get YTD values as below. Advantage is ...you need not create new time members to hold YTD values.

#XXX = SUM(YTD(),[TIME].[2010.JAN]) - SUM(YTD(), [TIME].[2010.FEB])

3) In the excel front end itself, use EVGET to populate YTD values. Then subtract using the normal excel formula.

If you can share the script logic you wrote to calcualte YTD, then we can suggest better option.

[Former Member]

Done via stored procedure.