TM1 Forum

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?

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

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?

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

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'];

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

I understand why you would feed ['average bor'] into the income accounts but why would you feed ['average bor'] into the other calculation variables?

If the rules are working but the results fail to consolidate then you may need
['Total income'] = ConsolidateChildren('gl account');

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.

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
should be
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
you could just reference the DB value directly.


Regards

Paul

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.