Months Dimension (Previous Period & Next Period)

[jimicron]

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

[jim wood]

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.

[Duncan P]

You could do it with rules (using DIMIX and DIMNM as described in the rules guide). 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.

[Martin Ryan]

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.

[jimicron]

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!

[jim wood]

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: Select all

['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

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: Select all

['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.

[jimicron]

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: Select all

['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: Select all

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

[declanr]

Code: Select all

['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.

[jimicron]

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

[jimicron]

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: Select all

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

[jimicron]

In rereading this, not sure I am clear. Still struggling with this after many attempts to do this various ways.

In the screenshot above, it appears that I have it figured out, but I do not. I have manually typed Actual or Forecast in the "Actual or Forecast" column. However, I'd like to do that via a rule driven off the "Current Month" column.

So, what is in Yellow and Green, I'd like to populate with a rule based off "Current Month" but using "Date Order String"?

Thanks again!

[declanr]

Jimicron,

Since the rule you have for 'Date Order String' is working and does what you need it to then you have a success on your hands there but for future reference you could make things easier on yourself by using "lookup dimensions" etc that would give the order of your months within a generic financial year rather than having to specify the first few months explicitly, there are also a few other ways you could do it that would reduce the size of your rule and possibly make it a bit easier to follow but at the end of the day it is working so you can ignore all that.
Just be careful as I know that TI scripts have (or at least did in 9.5.2) a limit of 20 nested IF statements and assuming rules are the same, you are getting quite close there... just one to remember in the future.

Code: Select all

['Actual or Forecast']=S:
               If ( Numbr ( AttrS ( 'Months', !Months, 'Date Order String' ) ) <= Numbr ( AttrS ( 'Months', DB ( 'control_cub', 'current_month', 'string' ), 'Date Order String' )), 'Actual', 'Forecast' );

The above should point you in the right direction, assuming you have a control cube as specified in the middle there. Also the above code would consider the "current" month to be actuals, sometimes people forecast the current month - if that's the case just replace the "<=" with a "<".

HTH

[jimicron]

Thanks!

I do have a 'control cube' ... it's a simple cube...

I tried the code that you gave... it works without an error... however, "Total Years" has "Actual" and all the rest have "Forecast" ?

Code: Select all

['Actual or Forecast']=S:
		 IF ( NUMBR ( ATTRS ( 'Months', !Months, 'Date Order String' ) ) 
	 	<= NUMBR ( ATTRS ( 'Months', DB ( 'Current Period', 'Current Month ID', 'Period' ), 'Date Order String' )), 
		 'Actual', 
		 'Forecast' );

Thanks for your patience!

[jimicron]

Trying to break it down....

When I do this only:

Code: Select all

['Actual or Forecast']=S: ATTRS ( 'Months', !Months, 'Date Order String' ) ;

It takes the string that is in the "Date Order String" column and puts it in the "Actual for Forecast" column.
Thus, 'Sep-12' has '1303' in both columns, 'Oct-12' has '1304' in both columns, etc.

When I do only this:

Code: Select all

['Actual or Forecast']=S: ATTRS ( 'Months', DB ( 'Current Period', 'Month', 'Period' ), 'Date Order String' );

I get '1315' for every month in the 'Actual or Forecast' column b/c my 'Current Period' is 'Jun-13' and the 'Date Order String' for 'Jun-13' is '1315'

In the syntax Declanr gives, I know then it's taking the NUMBR of each (when i do them individually, I have to just take the string since my column is string, not number).

I can't quite figure out why it's giving me 'Actual' for "Total Years" because by doing that, it's telling me that it's meeting the first condition and I don't see how 'rsN/A' is <= '1315' .... that's what is confusing.

[declanr]

Why are you using this:

Code: Select all

DB ( 'Current Period', 'Current Month ID', 'Period' )

In your code instead of this:

Code: Select all

DB ( 'Current Period', 'Month', 'Period' )

(which you did however use to prove your theory)


Have you tried right clicking on a cell and tracing calculations? It looks like the "Current Month ID" part of the calculation was coming back to a zero, hence everything being greater than it and being a forecast.

As to the total years "date order string" being an error, is that what you want or can you set it to blank etc? I'm not 100% but if the "Current Month ID" part is evaluating to 0 then I suppose the error for total years could become a zero when converted to a number... then they are both equal, hence "actual".

[jimicron]

Hi Declanr!

Thanks a TON for your help!! That actually resolved it.

What was I using "Current Month ID?" Well, , that was an accidental remnant of our troubleshooting yesterday. We did all sorts of things that I didn't even mention in the forum to spare the details. I accidentally copied over the wrong code that exposed that. Sorry about any confusion that may have created.

I think this just showed that we needed to step away because we had focused on this nearly all of yesterday and we missed an important little thing. Once I put it to what you gave, voila, it worked!!

I'm not sure why others haven't replied to this thread but I really really really appreciate all the help that you have provided!! We are very grateful for your assistance b/c it's been very helpful! Once we see how to do something once, we can then utilize it in other places. We still have one big hurdle to conquer, but this was a great one to conquer as well.

I'll be in London at the end of August. I owe you a couple rounds of beer!! (at least)

Thanks again!!!!

[tomok]

I'm not sure why others haven't replied to this thread

Why would we? Not everyone weighs in on every topic. If the issue was challenging and/or interesting you may have had a few more responses but this thread was neither. I don't know why you needed to go to all of this bother with the rules. I would have just added a numeric Index_Number attribute to the Month dimension, put a 1 in the first month, incremented by 1 for each consecutive month, and then written the Actual vs Forecast rule using the <= logic. It should have taken all of about an hour.

FWIW, I never write rules for attributes unless they have a 12-month or under payback. Time dimension attributes are never going to meet that criteria. You are probably going to reference them in other rules and strings (which most attributes are) are never cached. So, you waste time coding the attribute rules and take a performance hit when you use them. I mean, how often do you add periods? Once a year? Just add the new periods and populate the attributes manually. There is such a thing as over-automation.

[Alan Kirk]

I'm not sure why others haven't replied to this thread

Tomok has of course given you an answer to that in his inimically (sorry, I meant inimitably, I think) tactful and diplomatic fashion, known for winning friends and influencing people the world over. (Which is to say that you shouldn't take that one too personally.) But he's also prepared to say some truths which others might not, and I'm afraid that this is one of them. This is not to say that it was a bad question, but it was fairly basic, "meat and 'taters" style TM1 content.

For my own part one of the reasons that I didn't contribute is that over the years I've developed an almost unconscious bias against answering rules questions simply because so many people who ask them abstract the questions to the point of unrecognisability because they don't want to give up information about the structure of their cubes or data. (I'm not saying that you did, I'm talking generally.) I therefore generally skip over any question that concerns rules these days unless something about it really catches my eye, though I might have a crack at them if they've been sitting there for a day or two and, just as importantly, if I have the free time to work the question and try and figure out what the asker is trying to achieve. However by that time someone like Duncan or Declan or Wim or someone else will often have despatched it anyway.

Which leads to the second, and more important reason; you had replies from two admins plus a couple of MVPs already. And not trivial replies either. Given that, a casual glance at the thread would suggest that they pretty much had it covered. Consequently there wasn't much reason for the other admins / MVPs to jump in given that it wasn't really advanced or cutting edge content, and was already being more than covered by some of the heavy hitters. It's always important to remember that everyone here is contributing in their own not-unlimited time, so not every question is going to attract all of the more senior members every time.

(And I'm afraid that I have to agree with the other point that he raised, though others have mentioned it as well... this strikes me as being over-engineering, not to mention generating unnecessary calculations. Calendar maintenance as a once per year thing may as well be done manually. An .xdi and some excel-calculated DBSA's, it can be done in minutes. The time that you'd spend creating and documenting the rules would probably take years of manual calendar maintenance to repay.)

[jimicron]

Gosh - I didn't mean to start anything or rile any feathers, or not be gracious, or anything of the like. (with my comment that has been quoted twice now).

Yes, in looking back, others had replied to this - thank you! I guess my head was looking at it like this... Declanr was the only one that had replied recently and I felt bad that I was consuming so much of his time in the many responses he gave is all. My head was trying to just spread the wealth of questions and I merely felt bad b/c I really do appreciate the help this forum provides! I am hopeful that some day I will be able to return because now all I do is have questions. That's all I meant by that. I hope it didn't offend anyone. Certainly was not intent!

The training I have received has been a little bit to be desired... thus, why I may have some questions that appear 'basic' for most of you. Like I've said before, please bear with the newbie as you never know what a persons background, difficulties, limitations, or learning disabilities may be. Again, I have always really appreciated the help here!

There is more than meets the eye on this month dimension. Yes, typically, you can just populate once per year and I agree. What I was attempting to do is something similar to what we have in PowerPlay where we have all sorts of alternate hierarchies such as:

* Current Closed Period
* Prior Closed Period
* Current and Prior Closed Period
* Current Quarter
* Current Fiscal Year
* QTD
* YTD
* YTD Grouped
* Current FY and Next FY
* Current FY and Future 2 Years
* 8 Quarter Trend
* Future 2 Years
* Current Qtr and Previous 3 Qtrs
* 6 Month Trend
* 12 Month Trend
* 6 Month Trend and 12 Month Future
* 6 Month Trend and 6 Month Future
* 6 Month Trend and 24 Month Future
* etc.
* etc.

Thus, I was trying to derive all of those based on my simple 'control cube' that had "Current Period" in it and the end user would simply input the Mon-Yr (ie Jun-13) that was current and then my rules in the Months Attributes cube would then populate all those attributes I list above based on that one month. Since almost all of those above will roll on a monthly basis it would be quite a bit to have an end user update all of those attributes monthly.

I'll attach and image below so you can see a little bit. All the columns that are white, I am trying to create rules so they will all populate and roll based on what month is current. I have manually populated three columns (YTD, QTD, and Current Fiscal Year) just so you can see what I was hoping to populate the columns with. After these were all populated, I was going to create "Alternate Hierarchies" based on each of the attribute columns (I already have done that succesfully in other models and have the code for it).

Right or wrong, that is what I was trying to accomplish and why I was trying to do so.

[jimicron]

Here is a screenshot of the PowerPlay hierarchies that I was trying to replicate