Time consolidation - how to consolidate last year member

[abcuser]

Hi,
I am using Cognos TM1 v9.5.2. I created simple planning cube with the following hierarchy:
Time --> Year --> Month. On time dimension I would like to consolidate months to year. But consolidation from year to Time (top member) should be last year. I have manually written the following formula: Time=2012;
This formula returns correct value. But when new 2013 year will come I would need to manually change this formula to Time=2013; I would like to avoid this manual changing of formula. How to write this formula?

P.S. I have tried to write formula: ELCOMP('PL_PLAN_TIME','TIME',ELCOMPN('PL_PLAN_TIME','TIME'));
but I am getting error: "Error on line 2: Syntax error on or before: ('PL_PLAN_TIME','TIM missing colon"
For testing purposes have separated this formula into two sub formulas: ELCOMPN('PL_PLAN_TIME','TIME') returns 2. Formula: ELCOMP('PL_PLAN_TIME','TIME',2) returns the same above error. So there is some syntax error in this part of the formula that I can't figure it out what is wrong. See attached picture of sample planning cube.
Thanks

[Christopher Kernahan]

Hi,

You have a couple of options for this, some of which have been discussed before in the Forum.

You can create a System Info cube, include an element called 'Current Year', and point the rule reference to this cube using a DB() reference in the Rule, or

Set an attribute on the Time dimension elements called 'Current Year' and use and ATTRS() reference in the rule file.

[lotsaram]

... or don't have a rule at all and based on the "current year" value in whatever system cube use TI to spin through your PL_PLAN_TIME dimension and set the component weighting of the last full year to 1 and all other years to 0.

(or even, since this is a once per year rollover task just do the component weightings update manually either in dimension editor or xdi)

[abcuser]

Hi,
it looks like ELCOMP function can't be used without DB function or something... strange, but this is the way it looks like it works.

I have added below rule to cube (referencing the same cube in DB function as current cube - so only single PL_PLAN cube solution):

Code: Select all

SKIPCHECK;
['PLAN QUANTITY','TIME']=
C: 
DB(
   'PL_PLAN',
   !PL_PLAN_PRODUCT,
   ELCOMP('PL_PLAN_TIME','TIME',ELCOMPN('PL_PLAN_TIME','TIME')),
   'PLAN QUANTITY'
  )
;

Thanks for help

[tomok]

it looks like ELCOMP function can't be used without DB function or something... strange, but this is the way it looks like it works.

Nothing strange about that. Any time you are referencing a value from a cube in the right hand side of a rule, and you are using a function to return one or more dimension elements, then the entire cube reference must use the DB notation instead of the shorthand way. TM1 has worked this way since rules were first introduced years ago. How could you be a TM1 developer for at least two years (based on your join date), and not know this????

[abcuser]

@tomok, what is strange to me is why does ELCOMPN function works without DB notation and ELCOMP returns syntax error? They are siblings functions aren't they? So because ELCOMPN worked OK, and ELCOMP returned syntax error, I was investigating into a syntax error like missing comma or something like that. Error messages in TM1 can sometime be very confusing like "syntax error on or before <some_string>"...

[rmackenzie]

So because ELCOMPN worked OK, and ELCOMP returned syntax error, I was investigating into a syntax error like missing comma or something like that.

ELCOMP returns a string, whereas ELCOMPN returns a numeric. You have to have string measures to work with string rules. PLAN_QUANTITY is a numeric measure so any rule for ['TIME', 'PLAN_QUANTITY'] can only resolve to a numeric.

[tomok]

@tomok, what is strange to me is why does ELCOMPN function works without DB notation and ELCOMP returns syntax error? They are siblings functions aren't they? So because ELCOMPN worked OK, and ELCOMP returned syntax error, I was investigating into a syntax error like missing comma or something like that. Error messages in TM1 can sometime be very confusing like "syntax error on or before <some_string>"...

No rules function can be used in a cube reference unless the cube reference uses the DB notation. I didn't say the entire rules statement, just any portion that contains a cube reference. So, if you had a statement like:

['SomeVersion','SomeAcct','Jan']=N:['Actual','100100','Jan'] * ELCOMPN('Period',!Period) + 1;

will work but:

['SomeVersion','SomeAcct','Jan']=N:['Actual','100100',ELCOMP('Period','Jan',1)];

will not. You would have to make it:

['SomeVersion''SomeAcct','Jan']=N:DB(Cube,'Actual','100100',ELCOMP('Period',!Period,1));

[Ara123]

Hi Everyone,

I need urgent help from you guys. I am facing same kind of issue. Actually, i am writing the rules function to roll up measure values to higher level. my requirement is the last transaction record/values to rolled up to month, qtr,year and all year. for referrence, I have attached the screenshots.
1. the last transaction date measure value rolled up to month consolidation
2. last month to QTR
3. last QTR to year
4. last year to All year like that.
for that I have written rules and achived but,whenever the latest record comes, I need to modify the rules function. because i have hardcoded the date value.

I need to remove the hardcoding date value and it should find the elementg index and automatically update/roll up the measure.
This below function is working only when the month has completed dates. This function always find the last day of the month.
['actual_seats_quantity','Transaction_Date':{'2014-QTR3'}] =N: STET; C:DB('Reservation Cube', !Route,!marketing_flight_number, !operating_flight_number,!Cabin_Class,
!Flight_Date, !Flown_Status, ELCOMP('Transaction_Date', '2014-QTR3', ELCOMPN('Transaction_Date', '2014-QTR3')), 'actual_seats_quantity');

For incompleted month or current month, I need to write the below rules.
['Transaction_Date':{'2014-QTR3'},'Cabin_Class':{'All Cabins'}, 'Measure_Reservations':{'actual_seats_quantity'}]=C:['Transaction_Date':'SEP-2014'];
['Transaction_Date':{'2014-QTR3'},'Cabin_Class':{'J - Business'}, 'Measure_Reservations':{'actual_seats_quantity'}]=C:['Transaction_Date':'SEP-2014'];
['Transaction_Date':{'2014-QTR3'},'Cabin_Class':{'W - Premium Economy'}, 'Measure_Reservations':{'actual_seats_quantity'}]=C:['Transaction_Date':'SEP-2014'];

['Transaction_Date':{'2014'},'Cabin_Class':{'All Cabins'}, 'Measure_Reservations':{'actual_seats_quantity'}]=C:['Transaction_Date':'2014-QTR3'];
And etc…

Thanks
Ara

[rmackenzie]

Actually, i am writing the rules function to roll up measure values to higher level. my requirement is the last transaction record/values to rolled up to month, qtr,year and all year. for referrence, I have attached the screenshots.

Have you looked at trying to solve this problem at the reporting level, rather than the business logic level? I.e. assign an attribute to the time dimension e.g. 'Report From Period' or something - and then reference that period as part of the DBRW? I've put this sort of generic rule in before and it is a performance killer and complex to write if there are exceptions to the basic logic.