TM1 RULE not consolidating

Post Reply
DYNA
Posts: 21
Joined: Mon Jul 29, 2019 10:09 am
OLAP Product: PLANNING ANALYTICS (TM1)
Version: 2.0.6
Excel Version: Excel for office 365
Location: SOUTH AFRICA

TM1 RULE not consolidating

Post by DYNA »

Good day All,


I am having a challenge with the following rule
['Opening Balance'] = If((ATTRS('Calendar',!Calendar , 'IsMonthApril')@='Y' & ATTRS('Account - MDA', !Account - MDA, 'Flags')@='Y') ,0,DB('MDA Trial Balance ',!Calendar,!Version,!Data Source,!Client,!Property Sector,!Property Grade,!Property,!Account - MDA,'Opening Balance'));

I am trying to make opening balance zero since it is the beginning of the financial year , the rule does return the zeros for the specified cells but doesn't consolidate to the top level.

when i specify N: it doesn't not work at all .


Any direction or help would be much appreciated
Wim Gielis
MVP
Posts: 2898
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.10
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: TM1 RULE not consolidating

Post by Wim Gielis »

Hi

Did you use feeders ? And correctly ?
Best regards,

Wim Gielis

Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 120 TM1 articles and a lot of custom code
Newest blog article: Documenting processes and rules https://github.com/wimgielis
MarenC
Regular Participant
Posts: 219
Joined: Sat Jun 08, 2019 9:55 am
OLAP Product: Planning Analytics
Version: Planning Analytics 2.0
Excel Version: Excel 2016

Re: TM1 RULE not consolidating

Post by MarenC »

Hi,

Is MDA Trial Balance a different cube to the cube the rule is in?

Maren
DYNA
Posts: 21
Joined: Mon Jul 29, 2019 10:09 am
OLAP Product: PLANNING ANALYTICS (TM1)
Version: 2.0.6
Excel Version: Excel for office 365
Location: SOUTH AFRICA

Re: TM1 RULE not consolidating

Post by DYNA »

Thanks for responding , Actually I request an assistance with feeding this rule

Yes , MDA TRIAL BALANCE is different cube.
konstantin-spb
Posts: 9
Joined: Thu Feb 20, 2014 8:42 am
OLAP Product: TM1
Version: PA 2.0.9
Excel Version: 2016
Location: Russia, Saint-Petersburg

Re: TM1 RULE not consolidating

Post by konstantin-spb »

Hi,

Try something like this:

Source cube (in your code with a SPACE at the end of the cube name?):
'MDA Trial Balance '

Code: Select all

FEEDERS;

['Opening Balance'] => DB( 
    IF( ATTRS('Calendar', !Calendar, 'IsMonthApril') @= 'Y' & ATTRS('Account - MDA', !Account - MDA, 'Flags') @= 'Y',
        '',
    'Unnamed Cube', !Calendar, !Version, !Data Source, !Client, !Property Sector, !Property Grade, !Property, !Account - MDA, 'Opening Balance') ;
Target cube:
'Unnamed Cube'

Code: Select all

FEEDSTRINGS;
SKIPCHECK;

['Opening Balance'] = N:
    IF( ATTRS('Calendar', !Calendar, 'IsMonthApril') @= 'Y' & ATTRS('Account - MDA', !Account - MDA, 'Flags') @= 'Y',
        0,
    DB('MDA Trial Balance ', !Calendar, !Version, !Data Source, !Client, !Property Sector, !Property Grade, !Property, !Account - MDA, 'Opening Balance') );
Post Reply