TM1 Forum

Discussing all things TM1, Planning Analytics, PAx and PAW
https://www.tm1forum.com/

Rows with no values are not suppressed

https://www.tm1forum.com/viewtopic.php?t=2222

Page 1 of 1

Rows with no values are not suppressed

Posted: Mon Feb 22, 2010 2:21 am
by appleglaze28
I'd like to ask how come I encounter this how come when I click on the zero suppression, the rows without any values don't suppress. This usually happens along the way when I'm writing the rule for the cube but during data upload it doesn't happen.

Like for this example, my time dimensions with elements 01-19-2010, 01-27-2010 & 01-28-2010 are the only time element that should have any values for the 2 diemension elements indicated. But the date for 01-04-2010 even without value doesn't suppress.

Also how come even for the indicated time dimension element above, other rows with no value doesn't suppress during zero suppression.

What could be happening wrong?

Here's the rule for the cube though I doubt that its the rules cause missing feeders would have value be suppressed than the other way around like having zero values be unsuppressed during suppression.

Code: Select all

FEEDSTRINGS;

SKIPCHECK;
['VAT Rate']=N: IF(['Quantity']<>0,DB('general_so_setting',!base_sales_personnel,!base_so_number,'VAT Rate'),STET);		
['Working Margin']=N:  IF(['Quantity']<>0, DB('general_so_setting',!base_sales_personnel,!base_so_number,'Working Margin'),STET);			
['Minimum Target Margin']=N: IF(['Quantity']<>0, DB('general_so_setting',!base_sales_personnel,!base_so_number,'Minimum Target Margin'),STET);			


['VAT Rate']=C:['>>>VAT Rate Hold Value']\['>>>VAT Rate Count'];					
['Working Margin']=C:['>>>Working Margin Hold Value']\['>>>Working Margin Count'];
['Minimum Target Margin']=C:['>>>Minimum Target Hold Value']\['>>>Minimum Target Margin Count'];
['Exchange Rate (SO)']=C:['>>>Exchange Rate (SO) Hold Value']\['>>>Exchange Rate (SO) Count'];			
['Exchange Rate']=C:['>>>Exchange Rate Hold Value']\['>>>Exchange Rate Count'];			
['Adjusted Unit Price']=C:['>>>Adjusted Unit Price Hold Value']\['>>>Adjusted Unit Price Count'];			
['Unit Price']=C:['>>>Unit Price Hold Value']\['>>>Unit Price Count'];			
['Unit Cost']=C:['>>>Unit Cost Hold Value']\['>>>Unit Cost Count'];			


['>>>VAT Rate Hold Value']=N:['VAT Rate'];
['>>>VAT Rate Count']=N: IF(['VAT Rate']<>0,1,0);				
['>>>Working Margin Count']=N:IF(['Working Margin']<>0,1,0);
['>>>Working Margin Hold Value']=N:['Working Margin'];					
['>>>Minimum Target Margin Count']=N:IF(['Minimum Target Margin']<>0,1,0);
['>>>Minimum Target Hold Value']=N:['Minimum Target Margin'];
['>>>Exchange Rate (SO) Hold Value']=N:['Exchange Rate (SO)'];
['>>>Exchange Rate (SO) Count']=N: IF(['Exchange Rate (SO)']<>0,1,0);
['>>>Exchange Rate Hold Value']=N:['Exchange Rate'];
['>>>Exchange Rate Count']=N: IF(['Exchange Rate']<>0,1,0);
['>>>Adjusted Unit Price Hold Value']=N:['Adjusted Unit Price'];
['>>>Adjusted Unit Price Count']=N: IF(['Adjusted Unit Price']<>0,1,0);
['>>>Unit Price Hold Value']=N:['Unit Price'];
['>>>Unit Price Count']=N: IF(['Unit Price']<>0,1,0);
['>>>Unit Cost Hold Value']=N:['Unit Cost'];
['>>>Unit Cost Count']=N: IF(['Unit Cost']<>0,1,0);


['Incl.VAT Uplift']=S: IF(['Quantity']<>0, DB('general_so_setting',!base_sales_personnel,!base_so_number,'Incl.VAT Uplift'),STET);
['GOVT/ GOCC']=S:  IF(['Quantity']<>0, DB('general_so_setting',!base_sales_personnel,!base_so_number,'GOVT/ GOCC'),STET);
['No. of Day Provision for Cost of Money']=  IF(['Quantity']<>0, DB('general_so_setting',!base_sales_personnel,!base_so_number,'No. of Day Provision for Cost of Money'),STET);
['Lead #'] = S: IF(['Quantity']<>0, DB('general_so_setting',!base_sales_personnel,!base_so_number,'Lead #'),STET);
['Invoice #'] = S: IF(['Quantity']<>0, DB('general_so_setting',!base_sales_personnel,!base_so_number,'Invoice #'),STET);


['Adjusted Unit Price']=N:(((['TOTAL COST']\['TOTAL ITEM NUMBER','TOTAL COST'])*DB('general_so_setting',!base_sales_personnel,!base_so_number,'Total Selling Price'))\['Quantity']);		
['TOTAL COST']=N: (['Unit Cost']*['Exchange Rate (SO)'])*['Quantity'];
['Freight Charges']=N: ['TOTAL COST']*.04;
['Labor Uplift']=N: (['TOTAL COST']+['Freight Charges'])*.02;
['VAT Uplift']= N:IF(DB('worksheet',!base_client,!base_date,!base_number,!base_sales_personnel,!base_so_number,'Incl.VAT Uplift')@='Yes', (['TOTAL COST']*(1-['VAT Rate'])*.036),0);		
['Additional VAT Uplift']= N:IF(DB('worksheet',!base_client,!base_date,!base_number,!base_sales_personnel,!base_so_number,'GOVT/ GOCC')@='Yes', (['TOTAL COST']*(1-['VAT Rate'])*.05),0);
['TOTAL WORKSHEET COST']=N: ['TOTAL COST']+['Freight Charges']+['Labor Uplift']+['Additional VAT Uplift'];
['Unit Price']=N: ((['TOTAL WORKSHEET COST']\['Quantity'])\(1-['Working Margin'])\['Exchange Rate (SO)']);
['TOTAL SELLING PRICE']=N: IF(['Adjusted Unit Price']>(['Unit Price']	),((['Adjusted Unit Price']*['Exchange Rate'])*['Quantity']),((['Unit Price']*['Exchange Rate'])*['Quantity']));	
['VAT']=N: ['TOTAL SELLING PRICE']*['VAT Rate'];
['TOTAL INVOICE PRICE']=N: ['TOTAL SELLING PRICE']+['VAT'];
['Margin Before COM and POC']= ['TOTAL INVOICE PRICE']-['TOTAL WORKSHEET COST'];	
['Margin% Before COM and POC']=['Margin Before COM and POC']\['TOTAL SELLING PRICE'];
['Cost of Money (COM)']=['TOTAL SELLING PRICE']*['% of Cost of Money'];
['% of Cost of Money']=['No. of Day Provision for Cost of Money']\30;
['Prorated Other Cost']=['TOTAL SELLING PRICE']*(DB('other_cost',!base_date,!base_client,'TOTAL OTHER COST',!base_sales_personnel,!base_so_number,'Cost')\['TOTAL ITEM NUMBER','TOTAL SELLING PRICE']);
['Margin After COM and POC']= ['Margin Before COM and POC']-['Cost of Money (COM)']-['Prorated Other Cost'];
['Margin % After COM and POC']=['Margin After COM and POC']\['TOTAL ITEM NUMBER','TOTAL SELLING PRICE'];
['Target Indicator']= S: IF(['Margin % After COM and POC']<['Minimum Target Margin'], 'Margin Falls Below Minimum Target', 'Margin Acceptable');		




FEEDERS;
['Quantity']=>['Lead #'],['Invoice #'],['VAT Rate'],['Working Margin'],['Minimum Target Margin'],['Adjusted Unit Price'];



['VAT Rate']=>['>>>VAT Rate Count'],['>>>VAT Rate Hold Value'];	
['Working Margin']=>['>>>Working Margin Hold Value'],['>>>Working Margin Count'];
['Minimum Target Margin']=>['>>>Minimum Target Hold Value'],['>>>Minimum Target Margin Count'];
['Exchange Rate (SO)']=>['>>>Exchange Rate (SO) Hold Value'],['>>>Exchange Rate (SO) Count'];
['Exchange Rate']=>['>>>Exchange Rate Hold Value'],['>>>Exchange Rate Count'];
['Unit Cost']=>['>>>Unit Cost Hold Value'],['>>>Unit Cost Count'];
['Unit Price']=>['>>>Unit Price Hold Value'],['>>>Unit Price Count'];
['Adjusted Unit Price']=>['>>>Adjusted Unit Price Hold Value'],['>>>Adjusted Unit Price Count'];	




['Unit Cost']=>['TOTAL COST'];
['Exchange Rate (SO)']=>['TOTAL COST'];
['Quantity']=>['TOTAL COST'],['Unit Price'],DB('other_cost',!base_client,!base_date,!base_sales_personnel,!base_so_number,'TOTAL OTHER COST','Expense Limit');
['TOTAL COST']=>['Freight Charges'],['Labor Uplift'],['VAT Uplift'],['Additional VAT Uplift'],['TOTAL WORKSHEET COST'];
['Freight Charges']=>['Labor Uplift'],['TOTAL WORKSHEET COST'];
['VAT Rate']=>['VAT Uplift'],['Additional VAT Uplift'];
['Labor Uplift']=>['TOTAL WORKSHEET COST'];
['Additional VAT Uplift']=>['TOTAL WORKSHEET COST'];
['TOTAL WORKSHEET COST']=>['Unit Price'],['Margin Before COM and POC'];
['Working Margin']=>['Unit Price'];
['Adjusted Unit Price']=>['TOTAL SELLING PRICE'];
['Unit Price']=>['TOTAL SELLING PRICE'];
['TOTAL SELLING PRICE']=>['VAT'],['TOTAL INVOICE PRICE'];
['VAT']=>['TOTAL INVOICE PRICE'];
['TOTAL INVOICE PRICE']=>['Margin Before COM and POC'];
['Margin Before COM and POC']=>['Margin% Before COM and POC'];
['TOTAL SELLING PRICE']=>['Cost of Money (COM)'];	
['% of Cost of Money']=>['Cost of Money (COM)'];
['No. of Day Provision for Cost of Money']=>['Cost of Money (COM)'];
['Margin Before COM and POC']=>['Margin After COM and POC'];
['Cost of Money (COM)']=>['Margin After COM and POC'];
['Prorated Other Cost']=>['Margin After COM and POC'];
['Margin After COM and POC']=>['Margin % After COM and POC'];

['TOTAL','TOTAL ITEM NUMBER','Margin After COM and POC']=>DB('other_cost_conso',!base_client,!base_sales_personnel,!base_so_number,'TPC','Expense Limit'),DB('other_cost_conso',!base_client,!base_sales_personnel,!base_so_number,'Representation','Expense Limit'),DB('other_cost_conso',!base_client,!base_sales_personnel,!base_so_number,'TOTAL OTHER COST','% of Margin');
['TOTAL','TOTAL ITEM NUMBER','TOTAL SELLING PRICE']=>DB('other_cost_conso',!base_client,!base_sales_personnel,!base_so_number,'TPC','Expense Limit'),DB('other_cost_conso',!base_client,!base_sales_personnel,!base_so_number,'TOTAL OTHER COST','% of Sales');

Re: Rows with no values are not suppressed

Posted: Mon Feb 22, 2010 9:17 am
by Steve Rowe
Try this,

Change the rules that are like this
['VAT Rate']=N: IF(['Quantity']<>0,DB('general_so_setting',!base_sales_personnel,!base_so_number,'VAT Rate'),STET);

to

['VAT Rate']=N: IF(['Quantity']<>0,DB('general_so_setting',!base_sales_personnel,!base_so_number,'VAT Rate'),0);

The original of this rule is flicking the cells from ruled to input and back again. If you've ever had the cell populated with values then this can result in the feeder flag being left behind, so even if the cell is Stet it could have a feeder flag. The same is I think is true the other way round.
Say Quantity is greater than 0, this feeds the VAT Rate and the cell is then fed, everything works.
Quantity is now set to zero, this stops the rule working but does not remove the feeder. You now have an input fed cell with a zero in it.
This may well confuse the zero suppression engine.

Restarting the server would clear the flags.

If you have been doing lots of dev work on a cube it's good practice to resart the server anyway since this will clear any feeder flags that are in the cube that have come from feeders that don't exist anymore. The only way to be 100% sure that your system is in it's true state is to restart the server (or unload cubes in the simple case).

(As a small side point it seems like you are working with transactional records and building relational DB in TM1. This can be tricky and tends to through out strange problems.)

Anyway something to try not sure if it will work or not....

Cheers
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited