Zero Values on N Elements

[Horst]

Hi,

I've one more challenge.

My cube has two dimensions, measures and items with some c and n elements. One measure per example is "rebates", which should be planned for all items. The other measure "budget" is only planned on c element level. The requirement is, that the budet value on n elements isn't visible or just zero. If possible, I do not want to solve this by
cellsecurity. Is there a possibility to use a rule for that or do this cause a conflict with the TM1 calculation logic?

I've attached a screenshot where you can see how the result should be.

Thank you.

[Steve Rowe]

I think the answer to this will be no in a single cube, TM1 holds data at the N level so if you somehow made this zero then the C level value would be lost as well since there would be nothing to add up.

Off the top of my head something like this should work.
1. Create a budget entry cube that where the account dimension is summarised such that the data entry points are the N levels.
2. Rule the values down to the Actuals cube at the C level.

HTH...

[jwilkins]

You could define a rule along the lines of

Code: Select all

['budget']=N:0;

That would set all N elements to 0 for budget and because they are calculated, they would not be editable. But that's assuming you already have a C rule to calculate the budget for consolidations. Otherwise, all of the consolidations will just show 0.

[Horst]

Thanks for your fast Reply.

I think the answer to this will be no in a single cube, TM1 holds data at the N level so if you somehow made this zero then the C level value would be lost as well since there would be nothing to add up.

Off the top of my head something like this should work.
1. Create a budget entry cube that where the account dimension is summarised such that the data entry points are the N levels.
2. Rule the values down to the Actuals cube at the C level.

HTH...

I've already thought about your Suggestion before, but wouldn't that mean that I have to make a rule for every singe c element I got butget values for? So for every new Productgroup I have to expand the rule, right? I'm not sure whether this is administrable.

You could define a rule along the lines of

Code: Select all

['budget']=N:0;

That would set all N elements to 0 for budget and because they are calculated, they would not be editable. But that's assuming you already have a C rule to calculate the budget for consolidations. Otherwise, all of the consolidations will just show 0.

I don't have a rule for my budget consolidations - so that will not work for me.

[tomok]

Your issue is very common and is something that almost everyone wants that is building a budget model in TM1. The problem is that TM1 just doesn't work that way. Values are stored in N level elements. You can't budget at the C level (with the exception that you can enter a budget at the C level and allow TM1 to spread it to the N's underneath but in this case you are still storing at the N level).

You really have two options, and they both involve N levels, not C. You can pick an account underneath each C to be designated to hold the total budget for each rollup or you can create a pseudo item for the C items in question. For example, if the rollup is Sales Tax, with three children, but you want to budget at top level you can create a new item called Sales Tax_Entry, or something like that and have people enter it there instead. Neither option is perfect but are manageable. You can always control which accounts show up in a budget entry form or view by creating an attribute on the account called "Budget Account" and use that attribute to create a subset to base your form or view off. This way accounts not open for budgeting can be filtered out.

[David Usherwood]

You can't budget at the C level (with the exception that you can enter a budget at the C level and allow TM1 to spread it to the N's underneath but in this case you are still storing at the [C]N level).

Trying not to be picky but wanted to avoid confusion....

[declanr]

I've already thought about your Suggestion before, but wouldn't that mean that I have to make a rule for every singe c element I got butget values for? So for every new Productgroup I have to expand the rule, right? I'm not sure whether this is administrable.

You wouldn't need individual rules; you just have 1 rule relating where the left hand side specifies the relevant measures and uses an =C:
You then use an If statement that can be based on the level in this dimension alone if your hierarchy is not ragged... if it is ragged you can just make your If check that the consolidated element in your hierarchy returns an 'N' from a DType check against the dimension you use in your data entry cube.
When the If condition is not met it uses CONTINUE to proceed as it otherwise would have.

The other options that have since been suggeated are also good ones but I just thought I'd clarify that point so you can make a more informed decision on what best suits your requirements.

[Horst]

Thank you very much for your helpful suggestions!