TM1 Forum

Posted: **Mon Jul 02, 2012 4:45 am**

I've got a couple of consolidation values not calculating correctly and I'm trying to figure out why. This is a simple multiplication of units x rate to create sales, and for some reason everything works fine except at the consolidated level. Here are screenshots so you can see the behavior:

Calculations at a detail level:

You'll note that the Units Sold seems to aggregate properly, but the sales in Base Currency does not for the annual total.

Calculations at a consolidated level:

Note that Sales don't seem to aggregate at all.

Here is the relevant code:

Code: Select all

['Target Quantity','2013'] =
    DB('Sales Plan Final',!dimCustomersFinal,!dimProducts,!dimCostCenterFinal,!Versions,'Planned Qty');

['Current Yr Total Units'] = n:
    DB('Sales Plan Final',!dimCustomersFinal,!dimProducts,!dimCostCenterFinal,!Versions,'Planned Qty');

['Current Yr Total Units'] = c:
    DB('Sales Plan Final',!dimCustomersFinal,!dimProducts,!dimCostCenterFinal,!Versions,'Planned Qty');

['Calculated Units Sold']  = n:
    ROUNDP( ['Current Yr Total Units']*(['Last 12 Month Units']\['PY Total Units']),0);

['Calculated Units Sold', '2013']  = c:
	['Calculated Units Sold','Oct-2012'] +
	['Calculated Units Sold','Nov-2012'] +
	['Calculated Units Sold','Dec-2012'] +
	['Calculated Units Sold','Jan-2013'] +
	['Calculated Units Sold','Feb-2013'] +
	['Calculated Units Sold','Mar-2013'] +
	['Calculated Units Sold','Apr-2013'] +
	['Calculated Units Sold','May-2013'] +
	['Calculated Units Sold','Jun-2013'] +
	['Calculated Units Sold','Jul-2013'] +
	['Calculated Units Sold','Aug-2013'] +
	['Calculated Units Sold','Sep-2013'];

['Phasing Adjustment Required','2013'] =
C: IF(['Oct-2012'] +
	['Nov-2012']+
	['Dec-2012']+
	['Jan-2013']+
	['Feb-2013']+
	['Mar-2013']+
	['Apr-2013']+
	['May-2013']+
	['Jun-2013']+
	['Jul-2013']+
	['Aug-2013']+
	['Sep-2013']= 0, ['Target Quantity','2013'] - ['Units Sold', '2013'],
	['Target Quantity','2013'] - ['Calculated Units Sold','2013'] -
	(['Oct-2012']+
	['Nov-2012']+
	['Dec-2012']+
	['Jan-2013']+
	['Feb-2013']+
	['Mar-2013']+
	['Apr-2013']+
	['May-2013']+
	['Jun-2013']+
	['Jul-2013']+
	['Aug-2013']+
	['Sep-2013']));

['Units Sold'] =n:
['Calculated Units Sold'] + ['Phasing Adjustment Required'];

['Unit Price in Base Currency' ] = n: DB('Sales Plan Final', !dimCustomersFinal, !dimProducts, !dimCostCenterFinal, !Versions, 'Planned Price in Base Currency');

['Sales in Base Currency'] = n:
['Units Sold'] * ['Unit Price in Base Currency' ]

I've gone bleary-eyed looking at this... If anyone can point out the error in my calculations, it would be appreciated greatly!

Mike

Posted: **Mon Jul 02, 2012 5:52 am**

If doing any additions you want to use the hierarchy to calculate rather than doing a + throughout.

1st up check feedres from'Sales Plan Final' for ['Target Quantity','2013'] ['Current Yr Total Units'] ['Current Yr Total Units'] ['Unit Price in Base Currency' ] (right click on cells check feeders)

Unsure the reason ['Calculated Units Sold', '2013'] = c: rule for summing your units sold, but probably not feed, so right click check feeders, but anyway, remove the calc completely and let the month dim heiracrhy do the work. Just make sure you feed all the n level elements.

unsuer what you are doing with 'Phasing Adjustment Required', maybe comment this out in the meantime

Make ['Units Sold'] a parent of ['Calculated Units Sold'] & ['Phasing Adjustment Required'];

Check the feeder for ['Sales in Base Currency'] = n:
['Units Sold'] * ['Unit Price in Base Currency' ]

probably ['Units Sold'] => ['Sales in Base Currency'] ;

Posted: **Mon Jul 02, 2012 6:19 pm**

BigG wrote: ['Units Sold'] => ['Sales in Base Currency'] ;

That solved the problem... don't know how I missed it.

As an FYI, the phasing adjustment was designed to help the end user adjust units sold in a month when the rounding that occurs in spreading annual sales of a product over its prior history end up aggregating to a higher or lower number.

Mike

TM1 Forum

Help with consolidated values

Help with consolidated values

Re: Help with consolidated values

Re: Help with consolidated values