Hierarchy Roll up with sub account formulas

Post Reply
cdhodge2002
Posts: 90
Joined: Tue Aug 19, 2008 4:56 pm

Hierarchy Roll up with sub account formulas

Post by cdhodge2002 »

I am having issues with my Hierarchy rollups. So I have a Total income account, with sub accounts under that are calculated with formulas. When you look at the Total income amount it is zero even though the sub accounts are calculated correctly. Anybody ever run into this before?
User avatar
Martin Ryan
Site Admin
Posts: 1988
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: Hierarchy Roll up with sub account formulas

Post by Martin Ryan »

Sounds like either a feeder issue or you've got a rule that's over riding your natural consolidation. My guess would be the feeders. Check they're working correctly.

Cheers,
Martin
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
cdhodge2002
Posts: 90
Joined: Tue Aug 19, 2008 4:56 pm

Re: Hierarchy Roll up with sub account formulas

Post by cdhodge2002 »

What should I look for in the feeders? The sub accounts calculate correctly in off the variables that transfer in off the feeders. I have the skipchecks with the transfer data positioned first in the rules file, is this where it should be?
User avatar
Martin Ryan
Site Admin
Posts: 1988
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: Hierarchy Roll up with sub account formulas

Post by Martin Ryan »

If you put zero suppression on, do your sub accounts no longer appear? If that's the case then it's feeders and we can look into it, if they still appear then it's more likley a rogue C level rule. Can you post your rule and feeders?

Are the rules intra cube (only one cube involved) or inter cube?

Martin
Please do not send technical questions via private message or email. Post them in the forum where you'll probably get a faster reply, and everyone can benefit from the answers.
Jodi Ryan Family Lawyer
cdhodge2002
Posts: 90
Joined: Tue Aug 19, 2008 4:56 pm

Re: Hierarchy Roll up with sub account formulas

Post by cdhodge2002 »

So I tried the suppress zero and it eliminated and it says that there are no values to show. Here is an example of the rules:
['4000 - Appliance Rental Income' ] = ['Average BOR']*['Appliance Product Mix %']*['Appliances Average price per unit' ] ;
['4012 - Living Room Rental Income' ] =['Average BOR']*['Furniture Product Mix %' ] *['Furniture Average price per unit' ] ;
['4015 - Home Office Equipment Rental Income' ] =['Average BOR']*['Home Office Equipment Product Mix %' ] *['Home Office Equipment Average price per unit' ] ;
['4020 - Stereo Rental Income' ] =['Average BOR']*['Stereo Product Mix %' ] *['Stereo Average price per unit' ] ;
['4025 - Television Rental Income' ] =['Average BOR']*['Television Product Mix %' ] *['Televisions Average price per unit' ] ;
['4035 - Video Products Rental Income' ] =['Average BOR']*['Video Product Mix %' ] *['Video Products Average price per unit' ] ;
['4040 - Other Rental Income' ] =['Average BOR']*['Other Product Mix %' ]*['All Others Average price per unit' ] ;

All the Variables that calculate the gl accounts are formulas. the product % and price per units are coming in from another cube. Here is an example of those:
['Appliances as % of Deliveries' ]=N:DB('Budgeting Assumption Cube', !fr_fiscal_year, !fr_months, !fb_budget_version, !fr_store_number,'Appliances as % of Deliveries' );
['Appliances average price per unit' ]=n:DB('Budgeting Assumption Cube', !fr_fiscal_year, !fr_months, !fb_budget_version, !fr_store_number,'Appliances average price per unit' );
['Appliance Product Mix %']=['Appliances as % of Deliveries'];
Andy Key
MVP
Posts: 351
Joined: Wed May 14, 2008 1:37 pm
OLAP Product: TM1
Version: 2.5 to PA
Excel Version: Lots
Location: Sydney
Contact:

Re: Hierarchy Roll up with sub account formulas

Post by Andy Key »

As an absolute minimum you are going to need to feed the '4xxx - xxx Income' lines, so in your main cube you will need something along the lines of:

Code: Select all

Feeders;
['Average BOR']=>[{'4000 - Appliance Rental Income' ,'4012 - Living Room Rental Income' , <all the rest>}];
As an alternative, if all those lines have the same parent (or if they appear under different parents, you can add a dummy parent for all of them, outside your normal hierarchy) you can feed to the parent instead:

Code: Select all

Feeders;
['Average BOR']=>[<parent line>];
That should be enough to allow you to see the Income lines and, as these are now fed, their parents as well. At this point, you will not be able to see the data against the 'Appliances Average price per unit' type lines in your main cube if you have zero suppression on. As the totalling on driver numbers is possibly irrelevant, this may not be a problem. If it is a problem, and I see that you are calculating the income lines at N: and C: levels rather than totalling the N: levels, then you can always feed these lines from 'Average BOR' as well, from inside the same cube. So you can add:

Code: Select all

['Average BOR']=>[{'Appliances Average price per unit', 'Furniture Average price per unit', <etc>}];
Again, the same principle applies; if you have a parent for these driver lines, or if you can invent a parent for these driver lines you can feed to that instead:

Code: Select all

['Average BOR']=>[<parent driver line>];
Andy Key
cdhodge2002
Posts: 90
Joined: Tue Aug 19, 2008 4:56 pm

Re: Hierarchy Roll up with sub account formulas

Post by cdhodge2002 »

I understand why you would feed ['average bor'] into the income accounts but why would you feed ['average bor'] into the other calculation variables?
Mike L
Posts: 58
Joined: Fri Jun 13, 2008 4:01 pm

Re: Hierarchy Roll up with sub account formulas

Post by Mike L »

If the rules are working but the results fail to consolidate then you may need
['Total income'] = ConsolidateChildren('gl account');
cdhodge2002
Posts: 90
Joined: Tue Aug 19, 2008 4:56 pm

Re: Hierarchy Roll up with sub account formulas

Post by cdhodge2002 »

I have done that as a patch but the problem is maintenance because all of the hierarchies are not working. so any updates to the chart of accounts or stores will have to be updated in the rules as well.
User avatar
paulsimon
MVP
Posts: 808
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: Hierarchy Roll up with sub account formulas

Post by paulsimon »

Hi

I may be misunderstanding your data, but it looks to me as though you might have a problem with your rules as well as your feeders

I think you are missing the N: ie this

Code: Select all

['4000 - Appliance Rental Income' ] = ['Average BOR']*['Appliance Product Mix %']*['Appliances Average price per unit' ] ;
should be

Code: Select all

['4000 - Appliance Rental Income' ] = N: ['Average BOR']*['Appliance Product Mix %']*['Appliances Average price per unit' ] ;
Let me know if that makes sense.

This, combined with the feeder to the 4000's parent from Average BOR should get things working.

By the way, rather than pulling in

Code: Select all

 ['Appliances as % of Deliveries' ]=N:DB('Budgeting Assumption Cube', !fr_fiscal_year, !fr_months, !fb_budget_version, !fr_store_number,'Appliances as % of Deliveries' );
you could just reference the DB value directly.


Regards

Paul
Andy Key
MVP
Posts: 351
Joined: Wed May 14, 2008 1:37 pm
OLAP Product: TM1
Version: 2.5 to PA
Excel Version: Lots
Location: Sydney
Contact:

Re: Hierarchy Roll up with sub account formulas

Post by Andy Key »

cdhodge2002 wrote:I understand why you would feed ['average bor'] into the income accounts but why would you feed ['average bor'] into the other calculation variables?
As your calculations are currently being performed at both N and C level, and as you are picking up the driver lines from an external cube and storing them in your main cube before using them in the main calculation, you need to have fed these driver lines in the main cube as well.

Note: if your calculations should only be at N level, then you don't need to feed the driver lines. You just won't be able to see them in your main cube with zero suppression on.

But if your rule does apply to C levels as well, then the consolidated value of your driver line will be being used in the consolidated calculation. So you need to feed the driver lines in the main cube, to allow them to consolidate.

You could feed them from the Budgeting Assumptions Cube, but this could lead to overfeeding. However, as all your calculations rely on Average BOR being non-zero, we only need to feed the driver lines where this is the case. So if we use Average BOR to feed the driver lines as well, we will minimise the number of feeders.
Andy Key
Post Reply