YTD calculation(TI)

[David331]

Hi,

I have to create YTD value in cube.How do I get my weeks to Cumulate into YTD inclusinf present week also. Can anybody tell me the process to do this in TI without using rule.Do we have to create the TI script in the Prolog or metadata of TI?? I am new to Tm1 cube development.

[lotsaram]

It sounds like you are making things unnecessarily difficult. Usually you would calculate a YTD with a dimension consolidation in your time dimension. Then you don't need to "do" anything to get YTD into the cube (or in fact all cubes using the same time dimension) it will just be there.

[David331]

Thanks a lot for the reply. I now understand the creation of other hierarchy for YTD in time dimension with appropriate consolidations which will solve the problem. But how is it possible to maintain dynamically for the current Week and previous week. Please find the attached screenshot if this process is correct. Is it that the consolidation to be done for 52 weeks of year as per screenshot.Other query is how do I calculate the 6 weeks moving average for the cube. Any idea or suggestion please.

Thanks in advance!!

[paulsimon]

Hi

I generally work with months but the principle for weeks is the same.

I would suggest that you create your dimension as follows

For Week 1, create Week 1 YTD as a consolidation of Week 1.
For Week 2, create Week 2 YTD as a consolidation of Week 2 and Week 1 YTD.
For Week 3, create Week 3 YTD as a consolidation of Week 3 and Week 2 YTD and so on.

You can use a similar technique for 6 weeks rolling.

Week 6 Rolling is a consolidation of Week 1 .. Week 6
Week 7 Rolling is a consolidation of Week 2 .. Week 7

You can generate this in TI using a WHILE loop. Although I personally use VBA to generate a file and then process that using TI

What about the problem of the first 5 weeks? Well I suggest you see previous posts on using one time dimension for year and week / month combined vs using two dimensions, one for year and one for week / month. I would recommend the former.

I don't remember if the previous posts covered this but the reason why others have suggested using consolidation rather than rules for this is that consolidation is 100 times faster than rules, and it requires no feeding and therefore requires no additional memory.

Regards

Paul Simon

[David331]

Hi Paul,

Thanks a lot for your reply. I understand the YTD creation from your post. I will do the consolidation for rolling weeks in the same dimension but i have to calculate the average of that. Regarding the Weeks rolling average can you please provide some insight like where do we create the While loop. Is it in the Metadata tab or the prolog tab and also i have idea of VBA , but how can we implement and process through the Turbo Integrator?

[David331]

Hi paul,

can you please give me some more idea on this...

[Wim Gielis]

Paul was making the point that VBA (or plain Excel formulas) could generate elements and consolidations in a 2-column spreadsheet:
column A being the consolidated level, column B being the N-type element (or a consoldiation in itself).

The TI process will then be very simple; the Metadata tab will insert, row after row, combinations of a parent and a child.

Please note that bumping your thread is quite useless these days. Not a lot of activity takes place now.
You'd better go out, do the stuff in TI / Excel, and report back if you encounter any issues with coding.