I have a view as in below screenshot .
I want to apply 2 rules to an element . Is it possible to do so .
SO one rule is
['Ending Balance']=IF(NUMBR(ATTRS('Months', !Months, 'Month Number')) > 201601,['Beginning Balance']+['ALL Movement Types'],stet);
And other rule is based on other dimension stock category .
If stock category is blank then my calculation of total extended cost should overwrite the normal calculation.
it should be Total Qty * Costof current Month.
Now how should i apply this rule :
[ENding Balance, stockcategory:'Blank']=totalt qty*cost .
I directly added the rule to other dimension where calculation needs to be applied.
['Inventory Stock Category':'Blank','Total Extended Cost']= N:IF(NUMBR(ATTRS('Months', !Months, 'Month Number')) > 201601,['Total Qty on Hand']*DB('Cost Sets', '1100', '10', !Currencies, !Months, !Materials, 'Standard', 'Cost - Standard Price'),stet);
Can I apply these 2 to ending balance
Applying 2 rules to one item
-
- Posts: 128
- Joined: Tue May 23, 2017 12:45 pm
- OLAP Product: Tm1
- Version: 9.5
- Excel Version: 2010
Applying 2 rules to one item
- Attachments
-
- View1.png (10.67 KiB) Viewed 3169 times
-
- Posts: 128
- Joined: Tue May 23, 2017 12:45 pm
- OLAP Product: Tm1
- Version: 9.5
- Excel Version: 2010
Re: Applying 2 rules to one item
so my rules when tested are in order
['Ending Balance','Inventory Stock Category':'Blank','Total Extended Cost']= N:IF(NUMBR(ATTRS('Months', !Months, 'Month Number')) > 201601,['Inventory Stock Category':'Blank','Total Qty on Hand']*DB('Cost Sets', '1100', '10', !Currencies, !Months, !Materials, 'Standard', 'Cost - Standard Price'),stet);
['Ending Balance']=IF(NUMBR(ATTRS('Months', !Months, 'Month Number')) > 201601,['Beginning Balance']+['ALL Movement Types'],stet);
Feeders:
['Movement Types':{'Beginning Balance','ALL Movement Types'}] =>['Ending Balance'];
['Total Qty on Hand']=>['Total Extended Cost'];
Now I expect for Ending balance,blank ,extended cost , the value to use rule 1 ,were it multiplies the qty 3, by cost which should be around 8400, but here it take rule1 .
Why is my rule fr blank not applied here.
['Ending Balance','Inventory Stock Category':'Blank','Total Extended Cost']= N:IF(NUMBR(ATTRS('Months', !Months, 'Month Number')) > 201601,['Inventory Stock Category':'Blank','Total Qty on Hand']*DB('Cost Sets', '1100', '10', !Currencies, !Months, !Materials, 'Standard', 'Cost - Standard Price'),stet);
['Ending Balance']=IF(NUMBR(ATTRS('Months', !Months, 'Month Number')) > 201601,['Beginning Balance']+['ALL Movement Types'],stet);
Feeders:
['Movement Types':{'Beginning Balance','ALL Movement Types'}] =>['Ending Balance'];
['Total Qty on Hand']=>['Total Extended Cost'];
Now I expect for Ending balance,blank ,extended cost , the value to use rule 1 ,were it multiplies the qty 3, by cost which should be around 8400, but here it take rule1 .
Why is my rule fr blank not applied here.
- Attachments
-
- view4.png (29.55 KiB) Viewed 3155 times
-
- View3.png (48.77 KiB) Viewed 3155 times
-
- MVP
- Posts: 1815
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: Applying 2 rules to one item
Your first rule applies to N level elements only but you have consolidated elements selected in your view (such as all storage locations) that means the cell in question is not N level so the second rule which applies to N and C level takes effect instead.
If you change your view so all elements are N level you will see it working, unless ending balance is C level then there will never be a nodal cell that matches the criteria of the rule.
If you change your view so all elements are N level you will see it working, unless ending balance is C level then there will never be a nodal cell that matches the criteria of the rule.
Declan Rodger
-
- Posts: 128
- Joined: Tue May 23, 2017 12:45 pm
- OLAP Product: Tm1
- Version: 9.5
- Excel Version: 2010
Re: Applying 2 rules to one item
Hi,
I want the blank stock category rule to be applied to n and C level of ending balance
['Ending Balance','Inventory Stock Category':'Blank','Total Extended Cost']= IF(NUMBR(ATTRS('Months', !Months, 'Month Number')) > 201601,['Inventory Stock Category':'Blank','Total Qty on Hand']*DB('Cost Sets', '1100', '10', !Currencies, !Months, !Materials, 'Standard', 'Cost - Standard Price'),stet);
but if i again have the next rule which needs to sum the total , the above calculation will be overwridden.
['Ending Balance']=IF(NUMBR(ATTRS('Months', !Months, 'Month Number')) > 201601,['Beginning Balance']+['ALL Movement Types'],stet);
IS there a way to achieve this by not over writing the calculation .
I want the blank stock category rule to be applied to n and C level of ending balance
['Ending Balance','Inventory Stock Category':'Blank','Total Extended Cost']= IF(NUMBR(ATTRS('Months', !Months, 'Month Number')) > 201601,['Inventory Stock Category':'Blank','Total Qty on Hand']*DB('Cost Sets', '1100', '10', !Currencies, !Months, !Materials, 'Standard', 'Cost - Standard Price'),stet);
but if i again have the next rule which needs to sum the total , the above calculation will be overwridden.
['Ending Balance']=IF(NUMBR(ATTRS('Months', !Months, 'Month Number')) > 201601,['Beginning Balance']+['ALL Movement Types'],stet);
IS there a way to achieve this by not over writing the calculation .
- Attachments
-
- View3.png (48.77 KiB) Viewed 3145 times
-
- MVP
- Posts: 1815
- Joined: Mon Dec 05, 2011 11:51 am
- OLAP Product: Cognos TM1
- Version: PA2.0 and most of the old ones
- Excel Version: All of em
- Location: Manchester, United Kingdom
- Contact:
Re: Applying 2 rules to one item
TM1 rules apply in order, so if the first (more specific rule) is higher up in the rule sheet - that is the one that will apply. The second rule will only apply to cells that are not effected by the first rule statement.
Declan Rodger