Consolidation not working cause of skipcheck

Post Reply
mail_kupi
Posts: 22
Joined: Wed Jan 26, 2011 3:06 pm
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2003

Consolidation not working cause of skipcheck

Post by mail_kupi » Wed Mar 30, 2011 4:29 am

Hi Guys,
Please help me. i have cube that input in KG and i want to convert Price and Qty to another unit measures exp.DOOS
Price consolidate is not using aggregate or average but using Sales C: \ Qty C:

Code: Select all

SKIPCHECK;	

#Sales = Price * Qty		
['KG','Sales'] = N: ['KG','Price']*['KG','Qty'];

#Calculate Consolidate Price per Product
['KG','Price'] = C: ['KG','Sales']\['KG','Qty'];

['KG','Price'] = N: STET;
['KG','Sales'] = N: STET;
['KG','Qty'] = N: STET;

['Qty'] = N: ['KG','Qty'] \ DB('Convert',!Product,!Unit Measures);
['Price'] =N: ['KG','Price'] * DB('Convert',!Product,!Unit Measures);
['Sales'] =N: ['Price'] * ['Qty'];			
['Price'] = C: ['Sales'] \ ['Qty'] ;

FEEDERS;

['KG','Price'] => ['KG','Sales'],['Price'];
['KG','Sales'] => ['KG','Price'];
['KG','Qty'] => ['Qty'];
['Price'] => ['Sales'];
['Sales'] => ['Price'];
this rule running well in N element but the consolidate is not showing any value except KG unit measure. how i supposed to calculate consolidate unit measures except KG unit measure.

mikko
Posts: 10
Joined: Mon Sep 06, 2010 7:05 pm
OLAP Product: TM1
Version: 9.5.0
Excel Version: 2007

Re: Consolidation not working cause of skipcheck

Post by mikko » Wed Mar 30, 2011 5:56 am

Have you ever heard of "FEEDERS"? If not, I strongly recommend to learn the basics of rules and feeders for example from TM1 Rules Guide and TM1 Reference Guide.

User avatar
garry cook
MVP
Posts: 198
Joined: Thu May 22, 2008 7:45 am
OLAP Product: TM1
Version: Various
Excel Version: Various

Re: Consolidation not working cause of skipcheck

Post by garry cook » Wed Mar 30, 2011 7:08 am

Bit harsh - there's a clear attempt made to feed in the example.

Suspect your problem is due to the fact that you can't feed a consolidation through rules as feeding a consolidation is essentially shorthand for feeding the children of the consolidation. The only way you can feed a consolidation is by having values in the N: level points making up the consolidated value therefore you should concentrate on ensuring that there is a fed value somewhere beneath the consolidated points that are failing.

mail_kupi
Posts: 22
Joined: Wed Jan 26, 2011 3:06 pm
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2003

Re: Consolidation not working cause of skipcheck

Post by mail_kupi » Wed Mar 30, 2011 7:29 am

so what you mean there is something wrong with my rule that having value in N: level point?

mikko
Posts: 10
Joined: Mon Sep 06, 2010 7:05 pm
OLAP Product: TM1
Version: 9.5.0
Excel Version: 2007

Re: Consolidation not working cause of skipcheck

Post by mikko » Wed Mar 30, 2011 9:57 am

oops.. sorry. I didn't notice that you have some feeders in your example. My mistake :) So are trying to calculate average prices in consolidated levels? If that's the case you could try to solve this by adding some extra elements in your dimension and do the calculation for C-levels with those. I have resolved similar cases by adding one element (like 'PriceCount') to coun if there is a value in input measure (if value 1 else 0) and another element to sum up prices (like 'PriceSum'). Then you add c-level rule in your price dimension, like ['Price']= C:['PriceSum']\['PriceCount'] and corresponding feeders. Hope this helps :)

User avatar
Steve Rowe
Site Admin
Posts: 2048
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Consolidation not working cause of skipcheck

Post by Steve Rowe » Wed Mar 30, 2011 8:54 pm

With the way your rules are written you need the AllowSeperateNandCRules=T; parameter in your config file. (Check you config file docs I might not have the parameter exactly right)

The natural behaviour of the rule engine is to expect you to define the rules for the N and Cs on consecutive lines.
So instead of
['Price'] =N: ['KG','Price'] * DB('Convert',!Product,!Unit Measures);
['Sales'] =N: ['Price'] * ['Qty'];
['Price'] = C: ['Sales'] \ ['Qty'] ;

you should write something like the following (I might not have the syntax exactly right since I never work without the parameter in place)

['Price'] =N: ['KG','Price'] * DB('Convert',!Product,!Unit Measures);
C: ['Sales'] \ ['Qty'] ;
['Sales'] =N: ['Price'] * ['Qty'];

If you add the parameter to the server config file you (hopefully) will find that your rule starts to work, it's pretty standard to have this parameter in place as it allows greater flexability in the rule order.

If not then it will be as others have said, unless you have at least one input value or fed ruled value under the area where the consolidated rule applies then the consolidation won't be fed, although you would still see the calculation in an unsuppressed view. However since you I don't think you are even seeing the calculated value then it's a rule order problem that can be solved by adding the parameter.

Cheers,

Steve

mail_kupi
Posts: 22
Joined: Wed Jan 26, 2011 3:06 pm
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2003

Re: Consolidation not working cause of skipcheck

Post by mail_kupi » Thu Mar 31, 2011 4:09 am

Hi Mikko,

actually my need is want to get price not by average but using C: sales \ C: Qty so its different from C:Price \ count(price). any idea ?

Steve,
['Price'] =N: ['KG','Price'] * DB('Convert',!Product,!Unit Measures);
['Sales'] =N: ['Price'] * ['Qty'];
['Price'] = C: ['Sales'] \ ['Qty'] ;

its mean that i want to convert price to another unit measurement and then calculate sales first. after that i want to get price except KG unit measure by sales \ Qty

['Price'] =N: ['KG','Price'] * DB('Convert',!Product,!Unit Measures);
C: ['Sales'] \ ['Qty'] ;
['Sales'] =N: ['Price'] * ['Qty'];

correct me if im wrong, these rule will calculate price that not using sales from price except KG. isn't it?

i have already change the feeders :
['Price'] => ['Sales'];
['Sales'] => ['Price'];
['KG','Qty'] => ['DOOS','Qty'];
['DOOS','Price'] => ['DOOS','Price'];

and its show the consolidate value but i dont know somehow :
['Price'] = C: ['Sales'] \ ['Qty'] ;

this rule is not working.. price consolidate except KG is showing aggregate value and not consollidate sales / consolidate Qty.

User avatar
Martin Ryan
Site Admin
Posts: 1958
Joined: Sat May 10, 2008 9:08 am
OLAP Product: TM1
Version: 10.1
Excel Version: 2010
Location: Wellington, New Zealand
Contact:

Re: Consolidation not working cause of skipcheck

Post by Martin Ryan » Thu Mar 31, 2011 7:11 am

Steve Rowe wrote:With the way your rules are written you need the AllowSeperateNandCRules=T; parameter in your config file. (Check you config file docs I might not have the parameter exactly right)
mail_kupi wrote: this rule is not working.. price consolidate except KG is showing aggregate value and not consollidate sales / consolidate Qty.
I think Steve has hit your problem here. Did you look into that parameter?

Also, as another issue, it looks to me like you have some areas that you are writing rules for twice. Don't forget about rule precedence. The first rule that can be applied to a cell will be applied. All subsequent rules that also apply to that cell will be ignored.

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

mail_kupi
Posts: 22
Joined: Wed Jan 26, 2011 3:06 pm
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2003

Re: Consolidation not working cause of skipcheck

Post by mail_kupi » Tue Apr 05, 2011 7:36 am

Hi Martin,

I try to look the config file, but i cannot found it. is it tm1s.cfg file?
or the configuration is stated in rules guide documentation?

User avatar
Steve Rowe
Site Admin
Posts: 2048
Joined: Wed May 14, 2008 4:25 pm
OLAP Product: TM1
Version: 10.2.2., PAW
Excel Version: Nearly all of them

Re: Consolidation not working cause of skipcheck

Post by Steve Rowe » Tue Apr 05, 2011 5:10 pm

Hi Kupi,

The tm1s.cfg file is the one you are after that's right, if you look at the tm1 operations guide there is a section on the cfg file and the various options on it.

AllowSeparateNandCRules
Optional
Specifies expressions for N: and C: levels on separate lines using identical AREA definitions, maintaining the rules conventions of TM1 7.x.
For example,
['Budget','Argentina']=N:Expression;
['Budget','Argentina']=C:Expression;
are both valid rules statements when you include the AllowSeparateNandCRules parameter in the Tm1s.cfg file and set to T.
To set the parameter to T, add the following line to Tm1s.cfg:
AllowSeparateNandCRules=T

You'll need to restart the server for this to take effect.

Cheers,
Steve

mail_kupi
Posts: 22
Joined: Wed Jan 26, 2011 3:06 pm
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2003

Re: Consolidation not working cause of skipcheck

Post by mail_kupi » Wed Apr 06, 2011 10:23 am

Thanks A lot steve.. :D

Post Reply