Months Dimension (Previous Period & Next Period)

想想111

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!!

dada19891101

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.

cat-hao

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.

jhh6yf

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.

wyjzcom

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!

fuanxx

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.

mmkjzz

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!

wddr

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.

libo600

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!!!

xiphiasjoe

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!!