TM1 Forum

Discussing all things TM1, Planning Analytics, PAx and PAW
https://www.tm1forum.com/

What's the feeder for this rule

https://www.tm1forum.com/viewtopic.php?t=3834

Page 1 of 1

What's the feeder for this rule

Posted: Tue Jan 04, 2011 11:12 am
by comma

Code: Select all

['Grand Total', 'All Months'] = DB ('Headcount', !Year, !Version, !Function, !Status, !Category, !Department, !Position, !Base Location, 'December', !Grade, !Cost Center, 'Total Headcount');


['Average Headcount', 'All Months']	= (
                         DB ('Headcount', !Year, !Version, !Function, !Status, !Category, !Department, !Position, !Base Location, 'Quarter 1', !Grade, !Cost Center, 'Total Headcount') +
                         DB ('Headcount', !Year, !Version, !Function, !Status, !Category, !Department, !Position, !Base Location, 'Quarter 2', !Grade, !Cost Center, 'Total Headcount') +
                         DB ('Headcount', !Year, !Version, !Function, !Status, !Category, !Department, !Position, !Base Location, 'Quarter 3', !Grade, !Cost Center, 'Total Headcount') +
                         DB ('Headcount', !Year, !Version, !Function, !Status, !Category, !Department, !Position, !Base Location, 'Quarter 4', !Grade, !Cost Center, 'Total Headcount'))
				    ) 

				    \ 
				  
				   IF (!Version @= 'Base', 
					2,
					4);
Need some help here. The above Rule is for a Headcount Cube. The important Measure elements are Grand Total, Total Headcount, and Average Headcount.

The Total Headcount will display the total headcount at the end of each month, if the Month element type is numeric, and will display the total headcount of it's last child if the Month element type is consolidation. So, the Total Headcount for Quarter 1 is the same as the total headcount of March, and the Grand Total for All Months equal Total Headcount December and equal Total Headcount Quarter 4.
So, Grand Total is only used for All Months, while Total Headcount is used for all Month elements. I know that Grand Total is kind of useless in the first glance, but I need it.

The Average Headcount only calculated for element All Months and the value is calculated from the value of each quarter, not from each month. The divisor is 4, but for Base Version the divisor is two because Quarter 3 and 4 in Base Version contain no values.

The problem is, when Suppress Zero is activated, the Grand Total and Average Headcount are suppressed as well.
Here is my feeder. Can someone please check it?

Code: Select all

['Total Headcount','Quarter 1'] => ['Average Headcount','All Months'];
['Total Headcount','Quarter 2'] => ['Average Headcount','All Months'];
['Total Headcount','Quarter 3'] => ['Average Headcount','All Months'];
['Total Headcount','Quarter 4'] => ['Average Headcount','All Months'];

['Total Headcount', 'December'] => ['Grand Total','All Months'];
Element Quarter 1-4 are already fed properly but the Average Headcount and Grand Total are not

Re: What's the feeder for this rule

Posted: Tue Jan 04, 2011 4:18 pm
by ajain86
Update the rules to not use the DB function. The DB function is not required since you are not referencing a different cube and just pulling different values from the same cube.

['Grand Total', 'All Months'] = ['Total Headcount', 'December'];

['Average Headcount', 'All Months', 'Base'] = ( ['Quarter 1', 'Total Headcount'] + ['Quarter 2', 'Total Headcount'] + ['Quarter 3', 'Total Headcount'] + ['Quarter 4', 'Total Headcount'] ) \ 2;
['Average Headcount', 'All Months'] = ( ['Quarter 1', 'Total Headcount'] + ['Quarter 2', 'Total Headcount'] + ['Quarter 3', 'Total Headcount'] + ['Quarter 4', 'Total Headcount'] ) \ 4;

Re: What's the feeder for this rule

Posted: Tue Jan 04, 2011 11:14 pm
by Gregor Koch
On your question about the Feeder.
comma wrote:

Code: Select all

The Average Headcount only calculated for element All Months and the value is calculated from the value of each quarter, not from each month. The divisor is 4, but for Base Version the divisor is two because Quarter 3 and 4 in Base Version contain no values.
[/quote]

My guess is that your Feeder only doesn't work in cases were you don't have 'Total Headcount' in December, because that is the only month your are feeding from. In this case you also need a Feeder like

['Total Headcount', 'June'] => ['Grand Total','All Months'];

And to comment on the Rule Syntax and the way it is written:
The DB function is not only needed to reference to another cube.
And to simplify and improve the performance of the rule you could create a consolidation of Mar, Jun, Sep and Dec called 'Something Something Something Darkside' and write the rule

['Average Headcount', 'All Months'] = ['Total Headcount', 'Something Something Something Darkside'] \       
               IF (!Version @= 'Base', 
               2,
               4);

Apparently consolidations work faster the summation in a rule.

Cheers

Re: What's the feeder for this rule

Posted: Wed Jan 05, 2011 12:13 am
by Gregor Koch
I think I got caught up between purely answering your question and a TV series and the result would be... overfeeding.
Probably you are better off using a 'Full Year' N-Element in the Month dimension for your calculations and/or not feeding at all.
The latter being applicable if you don't need to zero suppress or query your averages.

Re: What's the feeder for this rule

Posted: Wed Jan 05, 2011 2:34 am
by comma
ajain86 wrote:Update the rules to not use the DB function. The DB function is not required since you are not referencing a different cube and just pulling different values from the same cube.

['Grand Total', 'All Months'] = ['Total Headcount', 'December'];

['Average Headcount', 'All Months', 'Base'] = ( ['Quarter 1', 'Total Headcount'] + ['Quarter 2', 'Total Headcount'] + ['Quarter 3', 'Total Headcount'] + ['Quarter 4', 'Total Headcount'] ) \ 2;
['Average Headcount', 'All Months'] = ( ['Quarter 1', 'Total Headcount'] + ['Quarter 2', 'Total Headcount'] + ['Quarter 3', 'Total Headcount'] + ['Quarter 4', 'Total Headcount'] ) \ 4;
I tried your suggestion, and the Grand Total and Average Headcount values are calculated correctly, but they're still suppressed when the Suppress Zero is activated.
As an extra information, these Grand Total and Average Headcount values are pulled to Headcount Variance cube, and the Headcount Variance cube is working perfectly. I found this to be very strange

Re: What's the feeder for this rule

Posted: Wed Jan 05, 2011 2:36 am
by comma
Gregor Koch wrote:I think I got caught up between purely answering your question and a TV series and the result would be... overfeeding.
Probably you are better off using a 'Full Year' N-Element in the Month dimension for your calculations and/or not feeding at all.
The latter being applicable if you don't need to zero suppress or query your averages.
Thanks for the replies, Gregor. But I don't really get what you mean by Full Year N-Element.
Isn't Full Year always a consolidation?

Re: What's the feeder for this rule

Posted: Wed Jan 05, 2011 3:59 am
by Gregor Koch
Hi
'Full Year' does not need to be a consolidation. You can create a N-Element that is representative for the full year without it being a consolidation.
Especially in your case in which you don't consolidate but calculate the average for the year rather than to consolidate the months, which is the nature of an average.
So if you don't need to show that average against the 'All Months' consolidation, this would be a solution that would avoid feeding every single month for the calculation of one value (average for the whole year).

All this is under the assumption that 'All Months' is a consolidation of the month elements.

Re: What's the feeder for this rule

Posted: Wed Jan 05, 2011 9:22 am
by comma
Thanks again Gregor, but If it's possible, I would prefer to not add a new element to Month dimension.
The case here is my calculation's already correct but the feeder is not.

Re: What's the feeder for this rule

Posted: Wed Jan 05, 2011 9:51 am
by comma
I've tried to use Trace Feeders, and it seems that this feeder :

Code: Select all

['Total Headcount','Quarter 1'] => ['Average Headcount','All Months'];
only feeds the Average Headcount of January, February, ..., till December, but it does not feed the All Months itself.

How can I feed a consolidation element then?

Re: What's the feeder for this rule

Posted: Wed Jan 05, 2011 10:14 am
by Steve Rowe
Hi ,,
You can't feed a consolidation, a consoldiation will automatically calculate if one of it's members is populated and fed.

Can you repost your rule set as it stands at the moment?

Cheers,
Steve

Re: What's the feeder for this rule

Posted: Wed Jan 05, 2011 10:25 am
by lotsaram
comma wrote:I've tried to use Trace Feeders, and it seems that this feeder :
['Total Headcount','Quarter 1'] => ['Average Headcount','All Months'];
only feeds the Average Headcount of January, February, ..., till December, but it does not feed the All Months itself.

How can I feed a consolidation element then?
Feeders by definition are exclusively leaf cell only. Feeding a consolidation is just shorthand for feeding all N level descendants of the consolidation. Be wary of feeding consolidations as it can easily lead to overfeeding.

Re: What's the feeder for this rule

Posted: Wed Jan 05, 2011 1:30 pm
by comma
Steve Rowe wrote: Can you repost your rule set as it stands at the moment?
OK, I've created a dummy cube called HC. Basically it has the same structure as my original Headcount cube, but I discarded the unnecessary dimensions and elements to simplify things.

The dimensions of HC and their elements are:
1. Year: 2010, 2011, 2012
2. Version: Version 1, Version 2, Version 3
3. Month: Jan-Dec + Q1-Q4 + All Months (the usual hierarchy)
4. Cost Center: 100ABC, 200DEF, 300GHI
5. HC Measure:
___ C: Total HC
______N: Starting
______N: Addition
______N: Reduction
___ N: Grand Total
___ N: Avg HC


How the cube works:
Starting can only be inputed for January, while the Starting value of other months are taken from the Total HC of its previous month.
Total HC of each month is a consolidation of Starting, Addition, and Reduction.

The Starting of each quarter equals the Starting of that quarter's first month. So, Starting Q1 equals Starting Jan, Starting Q2 equals Apr, and so on.
The Total HC of each quarter equals the Total HC of that quarter's last month. So, Total HC Q1 equals Total HC Mar, Total HC Q2 equals Total HC Jun, and so on.

The Starting of All Months equals Starting Q1, equals Starting January.
The Total HC of All Months equals Total HC Q4, equals Total HC Dec.

Grand Total and Avg HC only exist for All Months, while for other Month elements the value is 0 (to prevent the user from inputing any numbers).
Grand Total equals the Total HC Q4 and equals the Total HC Dec. So basically its value is the same with Total HC All Months, but I need as Grand Total for special purpose.

Avg HC is calculated from the sum of Total HC of each quarter divided by 4.


The Rules and Feeders:

Code: Select all

SKIPCHECK;

# the Starting of Q1-Q4 and All Months equal the Starting of its first child

['Starting'] = IF (ELLEV ('Month', !Month) <> 0,
		                  DB ('HC', !Year, !Version, ELCOMP('Month',!Month,1), !Cost Center, 'Starting'),
		                  CONTINUE);


# the Total HC of Q1-Q4 and All Months equal the Total HC of its last child

['Total HC'] = IF (ELLEV ('Month', !Month) <> 0,
		               DB ('HC', !Year, !Version, ELCOMP ('Month', !Month, ELCOMPN ('Month', !Month)), !Cost Center, 'Total HC'),
		               CONTINUE);


# the Starting of each month, except Jan, equal the Total HC of last month

['Starting'] =  N: IF (!Month @= 'Jan',
			                STET,
			                DB ('HC', !Year, !Version, ATTRS ('Month', !Month, 'Prev'), !Cost Center, 'Total HC'));


# Grand Total of All Months equal Total HC on Dec

['Grand Total', 'All Months'] = DB ('HC', !Year, !Version, 'Dec', !Cost Center, 'Total HC');


# Avg HC equal the sum of Total HC divided by 4

['Avg HC', 'All Months'] = (
			                       DB ('HC', !Year, !Version, 'Q1', !Cost Center, 'Total HC') +
			                       DB ('HC', !Year, !Version, 'Q2', !Cost Center, 'Total HC') +
			                       DB ('HC', !Year, !Version, 'Q3', !Cost Center, 'Total HC') +
			                       DB ('HC', !Year, !Version, 'Q4', !Cost Center, 'Total HC')
		                     )
			                   \ 4;


# lock Grand Total and Avg HC of each month

[{'Grand Total', 'Avg HC'}] = N: 0;



FEEDERS;

['Total HC'] => DB ('HC', !Year, !Version, ATTRS ('Month', !Month, 'Next'), !Cost Center, 'Starting');

['Total HC','Q1'] => ['Avg HC', 'All Months'];
['Total HC','Q2'] => ['Avg HC', 'All Months'];
['Total HC','Q3'] => ['Avg HC', 'All Months'];
['Total HC','Q4'] => ['Avg HC', 'All Months'];

['Total HC','Dec'] => ['Grand Total','All Months'];
The view:

Re: What's the feeder for this rule

Posted: Thu Jan 06, 2011 8:53 am
by Steve Rowe
Hi ,,
So the reason the Grand Total and AVG HC get suppressed is that they are consolidations with all zero and unfed values below them. To the TM1 calculation engine since the members of the consolidation are not populated the consolidation itself does not exist.

Your best bet is to something like this
(EDIT : You might need to tweak the !Year reference).

['Grand Total', 'DEC'] =N: DB ('HC', !Year, !Version, 'Dec', !Cost Center, 'Total HC');


# Avg HC equal the sum of Total HC divided by 4

['Avg HC', 'DEC'] = N:(
DB ('HC', !Year, !Version, 'Q1', !Cost Center, 'Total HC') +
DB ('HC', !Year, !Version, 'Q2', !Cost Center, 'Total HC') +
DB ('HC', !Year, !Version, 'Q3', !Cost Center, 'Total HC') +
DB ('HC', !Year, !Version, 'Q4', !Cost Center, 'Total HC')
)
\ 4;


# lock Grand Total and Avg HC of each month

[{'Grand Total', 'Avg HC'}] = N: 0;

Make sure you fed the DEC values in the normal way, the consolidation will then work as normal and show the correct total since only DEC is populated.

Hope this gives you an idea of the direction you need to go, C level rules are one of the more tricky aspects of rule writing!

Cheers,

Steve

Re: What's the feeder for this rule

Posted: Thu Jan 06, 2011 10:05 am
by comma
I see... very effective indeed. I never thought to assign the value to the December or any other months instead of the All Months.
Thanks Steve, I really appreciate your help.

And btw, what do you mean by this?
Steve Rowe wrote:(EDIT : You might need to tweak the !Year reference).

Re: What's the feeder for this rule

Posted: Thu Jan 06, 2011 10:18 pm
by Steve Rowe
The edit I made was just because I was not sure about your dimensions and it was early and I was tired!

( When you specify Dec on the left hand side of the rule that means that you will be looking at Dec on the right if you are using the ! reference, but if you have hard coded references you will be fine, plus !Year is a different dimension anyway.....)

Cheers!

Re: What's the feeder for this rule

Posted: Mon Jan 10, 2011 10:08 am
by comma
Steve Rowe wrote:The edit I made was just because I was not sure about your dimensions and it was early and I was tired!

( When you specify Dec on the left hand side of the rule that means that you will be looking at Dec on the right if you are using the ! reference, but if you have hard coded references you will be fine, plus !Year is a different dimension anyway.....)

Cheers!
OK, understood. Thanks again, Steve.
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited