N level vs C level rules for the same elements

[jasonjmnz]

In a TM1 forecast cube I'm working on, users enter a Price and Unit volume for each product, a third rule based element (Sales) is the calculation of price x units. I want this to happen at the N level but not at the C level. REASON: At the C level (All Products), it's taking the sum of Price x the sum of Units and incorrectly calculating Sales. Is it possible to eliminate the multiplication calculation at the C level and let everything sum naturally. I'm a Finance person that does a lot of the rule writing so pardon my ignorance.

[David Usherwood]

Replace = by =n:

As a general rule, all calculations should have n: on them. 'KPI' type rules (Gross Profit % etc) should have nothing. I'll stick my neck out and say that c: rules can cause many issues and are best avoided except in very specific situations.

[lotsaram]

Replace = by =n:

As a general rule, all calculations should have n: on them. 'KPI' type rules (Gross Profit % etc) should have nothing. I'll stick my neck out and say that c: rules can cause many issues and are best avoided except in very specific situations.

In this case make the rule qualifier "N:" is the correct course of action. But I disagree with the general assertion that C: rules should be avoided. They definitely have their place and can be very useful.

[harrytm1]

On another note, I have just installed 9.5.1 over 9.5. I noticed the rules that vary between c and n-elements over the same area behave differently.

E.g.
['Price'] = N: DB('Price', !Product, !Month, 'Price');
C: ['Sales Amount'] \ ['Quantity'];

It was working fine in 9.5. but now in 9.5.1, the rule for C-elements no longer work, it just sums up the n-element prices, which is of course wrong.

I also have another rule which should work for both n and c-elements, but it seems to now only work for n-elements while the c-elemnts are the sum of the leaves. I hope it's my fault and nothing to do with 9.5.1.

E.g. ['GP%'] = ['Profit'] \ ['Sales Amount'];

I will add AllowSeparateNandCRules=T in the tm1s.cfg file (is the default set to F?) and give it a go. From the example in the Op Guide, do I then have to rewrite the rule in this manner in stead of the above?

['Price'] = N: DB('Price', !Product, !Month, 'Price');
['Price'] = C: ['Sales Amount'] \ ['Quantity'];

[lotsaram]

Harry - I have tested similar rules in 9.5.1 and have no issue with C level rules being correctly applied. The issue sounds strange, have you tried restarting the server?

AllowSeparateNandCRules=T lets you write separate area statements for N and C rules. This is not required per se to have a distinct C level calculation that overrides natural consolidation or is different from the leaf calculation for a single area statement.

[Martin Ryan]

AllowSeparateNandCRules=T lets you write separate area statements for N and C rules. This is not required per se to have a distinct C level calculation that overrides natural consolidation or is different from the leaf calculation for a single area statement.

It was my understanding that this is what this parameter was for, allowing for N and C to be handled differently, as in the example above. Perhaps you mean there are other ways around the problem, as well as using this parameter?? I thought this was necessary, which has always been a bug-bear of mine. I can't think of a good reason to default it to F.

Martin

[lotsaram]

No I think I mean what I say, ... but to be more clear.

If AllowSeparateNandCRules parameter is missing then the default is False (i.e. AllowSeparateNandCRules=F). In which case as per Harry's example you definitely can still have a distinct calculation for consolidations versus leaves within one area statement. You just have 2 distinct qualifiers and expressions for the one area statement. As per Harry's example:

Code: Select all

['Price'] = N: DB('Price', !Product, !Month, 'Price');
C: ['Sales Amount'] \ ['Quantity'];

However if AllowSeparateNandCRules=T is added to the tm1s.cfg file then it is possible to use the above notation or you have the option of writing the rules with distinct area statements, that is:

Code: Select all

['Price'] = N: DB('Price', !Product, !Month, 'Price');
['Price'] = C: ['Sales Amount'] \ ['Quantity'];

For this example there wouldn't really be any point in writing the rule with separate area statements for N: and C: however the AllowSeparateNandCRules parameter does allow additional flexibility to write distinct rules for leaves versus consolidations where the area statement definitions are multi-dimensional and not 100% overlapping. In practice these are probably very rare cases.

Steve Rowe and I discussed this a while back here http://forums.olapforums.com/viewtopic. ... 82&start=0

[harrytm1]

Thank you both for helping out.

Just restarted the server with the parameter set to T. Both ways of writing the N and C rules work. On the surface, this parameter seems rather redundant since in its absence (i.e. default to F), we can still write rules to separate N and C area using the way i wrote in the 1st example.

My earlier problem also extends to a rule that applies to BOTH N and C i.e. ['Price'] = ['Amount'] \ ['Qty'] ONLY works for N-elements. C-elements still consolidates, which is not what i want. However, after I had restarted the server, the problem vanished miraculously even without me compiling or changing the rules.

[mce]

Last week, I have been struggling with the same issue of N and C rules on the same area not applying at the same time, and only the first one was working. Therefore I had to use ISLEAF to combine them in the same rule statement. Now I realized that AllowSeparateNandCRules has not been mentioned in the config file. Now I decided to go through all possible parameter in config file. Thanks for the topic and replies...

[Martin Ryan]

Ah, I had completely misunderstood the parameter.

Seems even more redundant now.

Martin