To show the last loaded value based on the period selection

[sreesuku]

Hi Team,

I have a period dimension with hierarchy Year --> Quarter --> Month
I have loaded a measure 'Headcount' at month level. I would always want to show the last loaded month value as the output for a period selection.

Eg : If I select period as 2012 I want the data of the last loaded month as output (Thats how it should be for a measure like headcount). Currently its showing aggregated values of all months as output. For quarters also it should display the values of the last loaded month values of that particular quarter as output.

In the given screen shot. If I select 2012, 8 should be displayed as headcount not 32 ( sum of all values). Similarly if the selection is Q1 output should be 5 (Last loaded month -March- value)

Regards
Sree

[MSidat]

sreesuku,

It looks like you need to have some consolidation Rules in for your Qtr and Year elements. You could do with attributes against the Measure Dimension to differentiate which measures is treated as such (Lets call this attribute 'Marker' and possibly an attribute against each month elements to denote which value to bring back i.e. 2012 will be Dec 2012, Q1 will be Mar 2012 this attribute could be called 'Balance Period'

Your Rule would be something like this:

[{'2012','Q1','Q2','Q3','Q4'}] = C:if(ATTRS('Measure',!Measure,'Marker')@='X',
DB('MyCube', ATTRS('Period', !Period, 'Balance Period'), !Measure),
Continue);

No Feeders will be required.

I would be interested to know how some of the other more experienced TM1'ers would do something like this especially when you have possibly one cube with both Balance Sheet and P&L Measures in it.

[sreesuku]

Hi,

Can you please elaborate on what should I do ? Should I create text attribute in my period dimension and measure?
Also I have a catch here, I cannot define a fixed value which should be shown for my year. For year it should show the last loaded value.
Eg : Today If there is data for Week 12, then for Week 12 should be displayed for year selection. Next week when week 13 has data, it should be displayed for year.

sreesuku,

It looks like you need to have some consolidation Rules in for your Qtr and Year elements. You could do with attributes against the Measure Dimension to differentiate which measures is treated as such (Lets call this attribute 'Marker' and possibly an attribute against each month elements to denote which value to bring back i.e. 2012 will be Dec 2012, Q1 will be Mar 2012 this attribute could be called 'Balance Period'

Your Rule would be something like this:

[{'2012','Q1','Q2','Q3','Q4'}] = C:if(ATTRS('Measure',!Measure,'Marker')@='X',
DB('MyCube', ATTRS('Period', !Period, 'Balance Period'), !Measure),
Continue);

No Feeders will be required.

I would be interested to know how some of the other more experienced TM1'ers would do something like this especially when you have possibly one cube with both Balance Sheet and P&L Measures in it.

[declanr]

Using MSidat's method to solve this problem, yes you would create string attributes in both the measures and the period.

The Measures attribute is simply something along the lines of "For the element does the Consolidated period equal the most recent period"... I would however call it something snappier such as "marker" or "turnip"

The Periods attribute is loaded against the consolidated elements and specifies the N-Level period element that they should show when the above measure marker is flagged.


As for the issue of the period attribute changing, that is very simple to solve.

If you load your weekly values by TI, add an extra line that updates via an ATTRPUTS

If you don't load your weekly values by TI... create a TI anyway that only does the ATTRPUTS and have it scheduled by a chore to run overnight at whatever time may be relevant.... or just change the attributes manually each week.

[sreesuku]

Using MSidat's method to solve this problem, yes you would create string attributes in both the measures and the period.

The Measures attribute is simply something along the lines of "For the element does the Consolidated period equal the most recent period"... I would however call it something snappier such as "marker" or "turnip"

The Periods attribute is loaded against the consolidated elements and specifies the N-Level period element that they should show when the above measure marker is flagged.


As for the issue of the period attribute changing, that is very simple to solve.

If you load your weekly values by TI, add an extra line that updates via an ATTRPUTS

If you don't load your weekly values by TI... create a TI anyway that only does the ATTRPUTS and have it scheduled by a chore to run overnight at whatever time may be relevant.... or just change the attributes manually each week.

Hi,

I have tried doing as per your method. But its taking a long time to open the cube view after the rule creation.I am somewhat new to TM1. Can you please validate if the method is correct or not.

I have tested it in my local server. I am just trying do only for the element 2011, for which I have created the 'balance period' attribute and entered the value '2011-12-30'

My Cube name is : gdi_sgf_new
Measure dimension is : gdi_sgf_new_measures
Measure element name is Monthly_PA_LFA (which is marked as X using the 'Marker' attribute)
Period dimension : period(ytd-qtd-mtd-wtd)
Period element for which I am testing is 2011

I have given the same attribute names as you mentioned.

My rule is as given below
['2011']=C:
If
(
ATTRS('gdi_sgf_new_measures','Monthly_PA_LFA','Marker')@='X',

DB('gdi_sgf_new',ATTRS('period(ytd-qtd-mtd-wtd)','2011','Balance Period'),

'Monthly_PA_LFA'),

Continue);

I am also attaching the attribute dimension details. Please validate them and let me know if anything need to changed to make the cube up and running.

[qml]

Adding a rule to any cube will slow it down as it will turn the default sparsity algorithm of TM1 off for that cube. To switch it back on add "SKIPCHECK;" as the first line in your rules (you can also add "FEEDERS;" as the last line in your rules for the future). This should restore good performance.

Also, please search for these keywords on this forum or in the TM1 documentation to learn more on this subject, as it's well worth it!

[sreesuku]

sreesuku,

It looks like you need to have some consolidation Rules in for your Qtr and Year elements. You could do with attributes against the Measure Dimension to differentiate which measures is treated as such (Lets call this attribute 'Marker' and possibly an attribute against each month elements to denote which value to bring back i.e. 2012 will be Dec 2012, Q1 will be Mar 2012 this attribute could be called 'Balance Period'

Your Rule would be something like this:

[{'2012','Q1','Q2','Q3','Q4'}] = C:if(ATTRS('Measure',!Measure,'Marker')@='X',
DB('MyCube', ATTRS('Period', !Period, 'Balance Period'), !Measure),
Continue);

No Feeders will be required.

I would be interested to know how some of the other more experienced TM1'ers would do something like this especially when you have possibly one cube with both Balance Sheet and P&L Measures in it.

Adding a rule to any cube will slow it down as it will turn the default sparsity algorithm of TM1 off for that cube. To switch it back on add "SKIPCHECK;" as the first line in your rules (you can also add "FEEDERS;" as the last line in your rules for the future). This should restore good performance.

Also, please search for these keywords on this forum or in the TM1 documentation to learn more on this subject, as it's well worth it!

Hi,
I have added skipcheck and feeders to the rule. Now the cube is opening, but the element value for which I am testing is showing zero. However Its lower level elements are showing values.

My rule is given below
SKIPCHECK ;

[{'2011'}]=C:
If
(
ATTRS('gdi_sgf_new_measures','Monthly_PA_LFA','Marker')@='X',

DB('gdi_sgf_new',ATTRS('period(ytd-qtd-mtd-wtd)','2011','Balance Period'),

'Monthly_PA_LFA'),

Continue);

FEEDERS;
['Monthly_PA_LFA']=>['2011'];

I am also attaching the cube view.
Please note. I have also added a Week ending values to the period dimension. Now the hierarchy is Year --> Quarter --> Month --> Week endings

[MSidat]

sreesuku,

Re-Look at your DB Statement, it appears to be missing some arguments.

Word of Advice. You are unlikely to always get a full solution on these forums when asking questions. Some elements of independent research/reading of rule guides/reference guides is expected. As QML also suggested regarding Skipcheck & Feeders. I woudl suggest to look at the Rules Guide for the DB Statement.

[MSidat]

Sreesuku,

Probably a bit more advanced, but you do not need the following feeder:

Code: Select all

['Monthly_PA_LFA']=>['2011'];

As you are putting a value into 2011 which is the same as one of the children of 2011 (2011-12-30) the Rule is self feeding. Besides you can never trully feed a consolidation element. With the feeder that you have put in you are in effect saying: whereever there is a value against 'Monthly_PA_LFA' then expect a value to appear in any of the "NODAL" children of 2011 which is inefficient and redundant.

[Ara123]

Hi Every one,
Hope everyone doing good!
I am also having the same requirement like the last date of the moth will be the month to date (month consolidation) and last month will be QTD and same Last QTD should be the YTD.
simply say that last transaction date value/count will be rolled up to MTD,QTD and YTD.
My requirement is same as what 'Sreesuku' raised.

My cube nane is: Reservation_Cube,
Dimensions are: !MKT_FlightNUM, !OperatingFlight, !Flown_Status, !Reservation_Date'.
Measure Dimension: Measure_Revenue(measure element: 'Cal Actual Seat').
I followed your previous discussion and created the Attribute text for both 'Reservation_Date' and 'Measure_Revenue' dimension.
and created the below rule. but, I am getting zero value.

SKIPCHECk;

[{'2014'},'Cal Actual Seat']=C:
If
(
ATTRS('Measure_Revenue','Cal Actual Seat','Marker')@='X',
DB('Reservation_Cube', !MKT_FlightNUM, !OperatingFlight, !Flown_Status, ATTRS('Reservation_Date','2014','Balance'),
'Cal Actual Seat'),Continue);

Guys, Please help me on this
Thanks
Ara123.