企业绩效管理网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 560|回复: 6

Performing YTD and other relative time calculations ...

[复制链接]

74

主题

421

帖子

580

积分

高级会员

Rank: 4

积分
580
QQ
发表于 2014-3-19 03:41:48 | 显示全部楼层 |阅读模式
Hi,
I am new to TM1 and looking for a solution to handle relative time dimensions in the cubes. I searched the whole forum, and it seems like there is a lot of good information about the topic, but for a person new to the tool ,it was not easy to understand the conversations clearly.

I am trying to create a basic income cube with accounts and locations dimensions. The data comes at a monthly granularity. I see that I can create a cube with years and months dimension and create YTD calculation manually by changing the calculations in the Month dimension manually each month. But obviously this is not the best approach.

What is the best approach to handle this kind of requirement, where we need to have a YTD calculation in the cube which shows the right amount based on the month that we are in?

Can you give me some clear direction or at least refer a good documentation to me about this topic.

Thanks a lot
回复

使用道具 举报

82

主题

406

帖子

593

积分

高级会员

Rank: 4

积分
593
QQ
发表于 2014-3-19 04:54:34 | 显示全部楼层
You should create another hierarchy reflecting the YTD roll up you would like and then reference them into a report or view.
回复 支持 反对

使用道具 举报

79

主题

383

帖子

562

积分

高级会员

Rank: 4

积分
562
QQ
发表于 2014-3-19 05:14:07 | 显示全部楼层
Steve Joffe wrote:You should create another hierarchy reflecting the YTD roll up you would like and then reference them into a report or view.


I'd really appreciate it if you gave little bit more details. When you say alternate hierarchy. do you mean within the same dimension? If yes, what should be my calculations? Can you give an example of such a dimension?
回复 支持 反对

使用道具 举报

81

主题

424

帖子

616

积分

高级会员

Rank: 4

积分
616
QQ
发表于 2014-3-19 05:54:25 | 显示全部楼层
Hi Tutak,

You Period/Time dimension should look like something like this:

2009
      >2009.Jan
      >2009.Feb
      >2009.Mar
      >2009.Apr
      >.....
      >.....
      >2009.Dec

then within the same Dimension you created your YTD hierarchy for each year like:

2009YTD
      >2009YTD.Jan
            >2009.Jan
      >2009YTD.Feb
            >2009.Jan
            >2009.Feb
      >2009YTD.Mar
            >2009.Jan
            >2009.Feb
            >2009.Mar

and so on....

Hope it helps!
回复 支持 反对

使用道具 举报

76

主题

403

帖子

586

积分

高级会员

Rank: 4

积分
586
QQ
发表于 2014-3-19 06:12:00 | 显示全部楼层
Please see the attached screen-shot of the sample Period dimension.

Hope this helps~!
回复 支持 反对

使用道具 举报

72

主题

394

帖子

558

积分

高级会员

Rank: 4

积分
558
QQ
发表于 2014-3-19 06:46:58 | 显示全部楼层
rkaif wrotelease see the attached screen-shot of the sample Period dimension.

Hope this helps~!


Rizwan, thanks for the screen shot. in this picture, is YTD2002 a sum of YTD200201, YTD200202, ...? If yes, then doesn't it have 200201 more than one time?

I think where I am struggling is more on the reporting side. Is there a way to have such a dimension where YTD is automatically calculated based on the current month that we are in (system date)?

Jan
Feb
Mar
Apr
May
Jun
Jul
Aug
Sep
Oct
Nov
Dec
YTD -> This is an automatic calculation. based on the current month it is a total of YTD months.
回复 支持 反对

使用道具 举报

70

主题

437

帖子

587

积分

高级会员

Rank: 4

积分
587
QQ
发表于 2014-3-19 06:47:08 | 显示全部楼层
Tutak,

In the sample as you can see YTD2002, YTD200201, YTD200202 etc are the consolidated elements. 200201 is a n-th level element and is appearing more than once in the screenshot due to the consolidations which we are creating in the Period dimension.

You have to load the monthly data once for each month and the Consolidations which we are creating here will do the job for you. For example if you are in month of March then you will have data in Jan, Feb and March. Other months (April & onwards) will have zero. So in March you will be able to see consolidated values for YTDJan, YTDFeb and YTDMarch & other YTDMonths will have zero
回复 支持 反对

使用道具 举报

您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

QQ|手机版|小黑屋|企业绩效管理网 ( 京ICP备14007298号   

GMT+8, 2020-10-31 01:28 , Processed in 0.082784 second(s), 11 queries , Memcache On.

Powered by Discuz! X3.1 Licensed

© 2001-2013 Comsenz Inc.

快速回复 返回顶部 返回列表