企业绩效管理网

 找回密码
 立即注册

QQ登录

只需一步,快速开始

查看: 1669|回复: 20

Months Dimension (Previous Period & Next Period)

[复制链接]

66

主题

395

帖子

544

积分

高级会员

Rank: 4

积分
544
QQ
发表于 2014-3-15 12:41:17 | 显示全部楼层 |阅读模式
Hi all,

We do not use the typical time dimension in the sense that it's embedded in our company to concatenate Mon-Yr versus separate Month vs Year dimension.

I have a bunch of attributes for this dimension and I also need to add "Previous Period," "Next Period," "Last Period," and "First Period."

Is there a way to do this via Rules in the Attribute cube for my months dimension? (versus just manually populating every month, quarter, and year)

The Index for the dimension is like this which poses challenges as well

Total Years (Index: 1)
FY-98 (Index:2)
Q1-98 (Index: 3)
Sep-97 (Index: 4)
Oct-97 (Index: 5)
Nov-97 (Index: 6)
Q2-98 (Index: 7)
etc.

I'm just trying to make this as automated as possible with rules. Thanks a lot!!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复

使用道具 举报

73

主题

406

帖子

585

积分

高级会员

Rank: 4

积分
585
发表于 2014-3-15 13:47:16 | 显示全部楼层
Create a numeric attribute for the periods. Take the last 2 digits so say 98. then do an if statement for the first 4 digits so say if(value='Jan',01.... Concat them and you 9801, 9802, etc. You'll also have 9712. This way you cab build a rule to see if the second value = you take 1 off the year value. You get the general idea.

That's one way I've done it in the past. I'm sure some of the guys have done it differently,

Jim.
回复 支持 反对

使用道具 举报

78

主题

397

帖子

582

积分

高级会员

Rank: 4

积分
582
QQ
发表于 2014-3-15 14:31:07 | 显示全部楼层
You could do it with rules (using DIMIX and DIMNM as described in ). However in order for it to work across year or quarter boundaries all your months must be in order in a single block.

The other thing to take account of is that this rule would be returning a string and strings never get cached, so every time it was used the calculation would be run again.

All in all you are much better off putting that logic into the TI process that updates your time dimension and writing the values into the attribute cube from there.

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

70

主题

437

帖子

587

积分

高级会员

Rank: 4

积分
587
QQ
发表于 2014-3-15 14:54:16 | 显示全部楼层
I've always had a big issue with the DIMIX/DIMNM method for two reasons.  The first (as outlined by Duncan) is that is relies on the dimension being in the correct order and there being no consolidations in the middle of the dimension, as that will mess up the count of dimix.

The other problem is that I've discovered it's quite a lot slower than the attributes method.  This depends a bit on how big the dimension is, but the dimix/dimnm functions seem to be slower than an attribute call, plus there's two of them, rather than one.

So I'd absolutely recommend the attributes method, either via TI process or via DBSA formulas in an xdi file.
回复 支持 反对

使用道具 举报

67

主题

355

帖子

520

积分

高级会员

Rank: 4

积分
520
QQ
发表于 2014-3-15 15:02:04 | 显示全部楼层
Thanks guys!

   Jim, I'm sorry... but I'm not fully following you    I had someone else look with me too and we were both (I think because we are new) struggling with the concept  so I apologize for taking more of your time to explain differently if you are able.

In regards to DIMNM and DIMIX... is there a function that can be used where you say something like... Look at only level 0, or look at only level 1, 2, 3, etc.?  

In that case, then you could say, look at level 0, then take the DIMIX, etc.... would something like that work?  I seem to be good at conceptualizing but then executing or figuring out what to do with my concept is where it gets difficult  

Thanks again!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

87

主题

373

帖子

564

积分

高级会员

Rank: 4

积分
564
QQ
发表于 2014-3-15 15:08:18 | 显示全部楼层
jim wood wrote:Create a numeric attribute for the periods. Take the last 2 digits so say 98. Then do an if statement for the first 4 digits so say if(value='Jan',01.... Concat them and you 9801, 9802, etc. You'll also have 9712.

Once you've created the numeric attribute add a rule to the created control cube. The add a rule like this where time element = Jan-2013:
Code: ['attribute'] = Numbr(Subst(!element,(long(!element)-4),4) | IF(Subst(!element,1,3)@='Jan','01',IF(Subst(!Element,1,3)@='Feb',02'...........,'N/A')) ;
                              #Will give you 2013                                   #Will give either 01,02,03,04,05,06,07,08,09,10,11,12

Final result for the example attribute = 201301
jim wood wrote:This way you cab build a rule to see if the second value = you take 1 off the year value. You get the general idea.

Rule on cube containing previous month
Code: ['Previous'] = S: IF(Subst(str(ATTRN('time',!time,attribute),6,0),4,2)@='01'     #Checks for January
                            ,'Dec-'|Str(Numbr(Subst(str(ATTRN('time',!time,attribute)),1,4))-1,2,0)    #Sets to Dec when Jan is true
                            ,IF((Numbr(Subst(str(ATTRN('time',!time,attribute)),4,2))-1)=1
                                  ,'Jan'
                                  ,IF((Numbr(Subst(str(ATTRN('time',!time,attribute)),4,2))-1)=2
                                        ,'Feb'....
                             |Subst(str(ATTRN('time',!time,attribute)),1,4)              #takes 1 off the month and concatanates to the year
                           ) ;

If the period is Jan-2013 it will return Dec-2012

You could even use a look up cube / alias to avoid some of the IF statements in there.

I hope that helps,

Jim.

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

84

主题

399

帖子

588

积分

高级会员

Rank: 4

积分
588
QQ
发表于 2014-3-15 15:27:25 | 显示全部楼层
Thanks a lot Jim.  That is helping me get to where I want to be, etc.

I am starting small... and then growing from there because I want to understand as I go... thus, why the statement below is much smaller than what you gave.

Code: ['Date Order'] =N: NUMBR(SUBST(!Months,(LONG(!Months)-1),2))+1;

"Date Order" is the new attribute that I added to my Date dimension (which is called 'Months').  

When in the rules editor, the above does not error when I click on the validate button.  However, in my attributes cube the column is white and it's just all zeros (0).    I am trying to understand why.  The attribute ("Date Order") is numeric.  

When i change the code to this:

Code: ['Date Order'] =S: SUBST(!Months,(LONG(!Months)-1),2);

I get values (the last two digits of what's in my months dimension) - which is good  

However, where I am going with this is for 4 months of the year, I want to add 1 to what it retrieves.  Thus, if "Sep-12", and it pulls "12" - I want to add 1 to it to make the number "13"

Maybe I need to step away but been hammering at this for hours now (embarassingly) and decided to finally post.  I suspect I am missing something simple  

Thanks a lot!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

66

主题

378

帖子

540

积分

高级会员

Rank: 4

积分
540
QQ
发表于 2014-3-15 15:31:23 | 显示全部楼层
Code: ['Date Order'] =S: Str (Numbr ( SUBST(!Months,(LONG(!Months)-1),2) ) + 1, 2, 0 );

Numeric attributes pull through as string in the }elementattributes cubes which can get a bit annoying sometimes, but all you needed to do was convert the numeric value back to a string at the end.

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

94

主题

397

帖子

609

积分

高级会员

Rank: 4

积分
609
QQ
发表于 2014-3-15 15:38:56 | 显示全部楼层
Thanks a LOT declanr!!!  You just made me smile and I was REALLY needing that!!  What you provided worked perfectly!!  I guess learning that just comes with experience huh?  Thanks again!!!
回复 支持 反对

使用道具 举报

81

主题

429

帖子

608

积分

高级会员

Rank: 4

积分
608
QQ
发表于 2014-3-15 15:42:23 | 显示全部楼层
Hey guys,

I've gotten the logic to work okay for creating a number for each month, quarter, year in sequential order.  Our Fiscal Year is from Sep through Aug which is why I have to change the first four months so the ordering works out okay... Just to show that:

Code: ['Date Order String'] = S:
   IF(SUBST(!Months,1,3) @= 'Sep', STR (NUMBR ( SUBST(!Months,(LONG(!Months)-1),2) ) + 1, 2, 0 ),
   IF(SUBST(!Months,1,3) @= 'Oct', STR (NUMBR ( SUBST(!Months,(LONG(!Months)-1),2) ) + 1, 2, 0 ),
   IF(SUBST(!Months,1,3) @= 'Nov', STR (NUMBR ( SUBST(!Months,(LONG(!Months)-1),2) ) + 1, 2, 0 ),
   IF(SUBST(!Months,1,3) @= 'Dec', STR (NUMBR ( SUBST(!Months,(LONG(!Months)-1),2) ) + 1, 2, 0 ),
                                                SUBST(!Months,(LONG(!Months)-1),2) ))))
   |
   IF(SUBST(!Months,1,2) @= 'FY'  , '01' ,
   IF(SUBST(!Months,1,2) @= 'Q1'  , '02' ,
   IF(SUBST(!Months,1,3) @= 'Sep' , '03' ,
   IF(SUBST(!Months,1,3) @= 'Oct' , '04' ,
   IF(SUBST(!Months,1,3) @= 'Nov' , '05' ,
   IF(SUBST(!Months,1,2) @= 'Q2'  , '06' ,
   IF(SUBST(!Months,1,3) @= 'Dec' , '07' ,
   IF(SUBST(!Months,1,3) @= 'Jan' , '08' ,
   IF(SUBST(!Months,1,3) @= 'Feb' , '09' ,
   IF(SUBST(!Months,1,2) @= 'Q3'  , '10' ,
   IF(SUBST(!Months,1,3) @= 'Mar' , '11' ,
   IF(SUBST(!Months,1,3) @= 'Apr' , '12' ,
   IF(SUBST(!Months,1,3) @= 'May' , '13' ,
   IF(SUBST(!Months,1,2) @= 'Q4'  , '14' ,
   IF(SUBST(!Months,1,3) @= 'Jun' , '15' ,
   IF(SUBST(!Months,1,3) @= 'Jul' , '16' ,
   IF(SUBST(!Months,1,3) @= 'Aug' , '17' ,
   'N/A')))))))))))))))));

BUT, the real purpose of this post is to take it further.  

Now, I want to say, for the 'Actual or Forecast' column, populate 'Actual' from the 'current month' all the way back otherwise, populate with "Forecast".

That way, they will populate a 'Current Period' cube each month simply with the current Mon-Yr and everything will feed off that.  They won't have to go in and update "Actual or Forecast" (and I have about 20 more columns that will populate automatically if I can get this understood.  Me and another coworker (we are both green and painfully learning slowly) have looked at this all morning and are coming up empty handed (we've been close) so I finally decided to post.  I want to always make a diligent effort to get this on my own before posting so when I post, it's a last ditch effort.  I greatly appreciate any assistance!!  Thanks a lot!!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有帐号?立即注册

x
回复 支持 反对

使用道具 举报

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

本版积分规则

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

GMT+8, 2020-11-24 15:16 , Processed in 0.152564 second(s), 12 queries , Memcache On.

Powered by Discuz! X3.1 Licensed

© 2001-2013 Comsenz Inc.

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