TM1 Forum

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

Rolling 12 Period over YTD Period Calculation

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

Page 1 of 1

Rolling 12 Period over YTD Period Calculation

Posted: Tue Jul 27, 2021 1:50 am
by michaelc99
Good Afternoon Everyone,

I am fairly new to TM1, so I am looking for some guidance on a particular business rule calculation. I built a 12-dimension Stats Cube that pulls balances from GL MAIN to populate seven account members. This is to allow for an "A divided by B" calculation. The challenge that I'm facing is that the formula I'm replicating is taking a Rolling-12 YTD Balance from Month 1 and dividing it by a Month 1 YTD Balance.


Jan Rolling 12
Account 1 $4,000,000
Account 2 $1,000,000

Jan YTD
Account 1 $500,000
Account 2 $2,500,000

Code: Select all

['Actual', 'Turns (Gross)'] = C:
['Actual', 'Total Departments', 'Cost of Goods Sold']  \  ['Actual','Total Departments', 'Inventory-FIFO (Gross)'];
The code above computes 4M \ 1M and returns 4 inventory turns for Jan Rolling 12 and 0.2 for Jan YTD. However, I want to take 4M divided by 2.5M and return 1.6 inventory turns either in Jan Rolling 12 YTD (or Jan YTD) intersection point. I could simply add the period dimension to the calculation, as shown below, but it's not dynamic which is what I am hoping someone could point me in the right direction.

Code: Select all

['Actual', 'Jan 21 YTD', 'Turns (Gross)'] = C:
['Actual', 'Total Departments', 'Jan 21 YTD Rolling 12-Month' , 'Cost of Goods Sold']  \  ['Actual','Total Departments','Jan 21 YTD', 'Inventory-FIFO (Gross)'];
Thank you!

Re: Rolling 12 Period over YTD Period Calculation

Posted: Tue Jul 27, 2021 7:44 am
by MarenC
Hi,

You didn't specify what you meant by dynamic, but I am assuming dynamic means for every month, and not just January.

If this assumption is correct then how about:

Code: Select all

['Actual', 'Turns (Gross)'] = C:
['Actual', 'Total Departments', Subst(!Period,1,10) | ' Rolling 12-Month' , 'Cost of Goods Sold']  \  ['Actual','Total Departments', !Period, 'Inventory-FIFO (Gross)'];
I assume your Jan 21 dimension is called Period.

If the above assumptions are incorrect then you will have to clarify.

Maren

Re: Rolling 12 Period over YTD Period Calculation

Posted: Tue Jul 27, 2021 12:49 pm
by michaelc99
MarenC wrote: ↑Tue Jul 27, 2021 7:44 am Hi,

You didn't specify what you meant by dynamic, but I am assuming dynamic means for every month, and not just January.

If this assumption is correct then how about:

Code: Select all

['Actual', 'Turns (Gross)'] = C:
['Actual', 'Total Departments', Subst(!Period,1,10) | ' Rolling 12-Month' , 'Cost of Goods Sold']  \  ['Actual','Total Departments', !Period, 'Inventory-FIFO (Gross)'];
I assume your Jan 21 dimension is called Period.

If the above assumptions are incorrect then you will have to clarify.

Maren

Hello Maren,

Thank you for the quick response! And, yes your assumption is correct. When I wrote "dynamic", I meant that the the code would run for every month and not just January. I'm going to test the code now and will let you know the results shortly.

Thank you again!
Michael

Re: Rolling 12 Period over YTD Period Calculation

Posted: Tue Jul 27, 2021 1:51 pm
by michaelc99
Good Morning Maren,

I added the code and changed !PERIOD to !PERIOD MAIN but, after saving the code, it returned syntax and missing colon responses (see attachments.) In
line 45, on the right-hand side of the equation, I can change !PERIOD MAIN to 'Period Main':'Control Total' which seems to be accepted in the rule, but the left-hand side of the equation is still throwing errors.

Thank you,
Michael

Re: Rolling 12 Period over YTD Period Calculation

Posted: Tue Jul 27, 2021 2:47 pm
by declanr
michaelc99 wrote: ↑Tue Jul 27, 2021 1:51 pm Good Morning Maren,

I added the code and changed !PERIOD to !PERIOD MAIN but, after saving the code, it returned syntax and missing colon responses (see attachments.) In
line 45, on the right-hand side of the equation, I can change !PERIOD MAIN to 'Period Main':'Control Total' which seems to be accepted in the rule, but the left-hand side of the equation is still throwing errors.

Thank you,
Michael
Hi Michael,

You cannot use the subst etc inside square brackets. So you will need to change the first part (numerator) into a DB formula instead.
For the second part (denominator) - does the "Period Main" dimension exist in the cube for which you are saving the rule?

Re: Rolling 12 Period over YTD Period Calculation

Posted: Tue Jul 27, 2021 3:04 pm
by MarenC
Hi,
Yes Declan is correct, this will have to be included within a DB statement.

Apologies for that.

I presume given they are both within square brackets the rule looks at the same cube, i.e. the cube in which the rule resides.

Maren

Re: Rolling 12 Period over YTD Period Calculation

Posted: Tue Jul 27, 2021 5:13 pm
by michaelc99
MarenC wrote: ↑Tue Jul 27, 2021 3:04 pm Hi,
Yes Declan is correct, this will have to be included within a DB statement.

Apologies for that.

I presume given they are both within square brackets the rule looks at the same cube, i.e. the cube in which the rule resides.

Maren
declanr wrote: ↑Tue Jul 27, 2021 2:47 pm Hi Michael,

You cannot use the subst etc inside square brackets. So you will need to change the first part (numerator) into a DB formula instead.
For the second part (denominator) - does the "Period Main" dimension exist in the cube for which you are saving the rule?
Yes, PERIOD MAIN resides in the STATS cube. I wrote external feeders from GL MAIN to STATS, then a rule in STATS to pull the balances into specific accounts. As an example (see code) I am moving the balances from GL MAIN into STATISTICS for Total Standard Costs. That is one of several data points that are used to calculate Cost of Goods Sold.

Code: Select all


GL MAIN CUBE RULE:

['Actual', 'REGION MAIN':'CONTROL TOTAL','ENTITY MAIN':'Control Total', 'CURRENCY MAIN':'USD', 'DEPARTMENT MAIN':'Control Total', 'COST CENTER MAIN':'CONTROL TOTAL', 'CHANNEL MAIN':'CONTROL TOTAL','PRODUCT CENTER MAIN':'Control Total', 'SITE MAIN':'Control Total', 'ACCOUNT MAIN':'Total Standard Costs','PERIOD MAIN':'Control Total','GL MAIN MEASURE':'Total'] => DB( 'STATISTICS', !'VERSION MAIN', !'REGION MAIN', !'ENTITY MAIN', !'CURRENCY MAIN', !'DEPARTMENT MAIN', !'COST CENTER MAIN', !'CHANNEL MAIN', !'PRODUCT CENTER MAIN', !'SITE MAIN', 'Total Standard Costs', !'PERIOD MAIN', !GL MAIN MEASURE);


STATS CUBE RULE:

['Actual', 'Total Standard Costs'] = N:
DB('GL MAIN','Actual',!REGION MAIN,!ENTITY MAIN,'USD',!DEPARTMENT MAIN,!COST CENTER MAIN,!CHANNEL MAIN,!PRODUCT CENTER MAIN,!SITE MAIN,'Total Standard Costs',!Period Main, !GL MAIN MEASURE);
Let me put some quick code together as it sounds like:

[Target Location] = n:
IF(SUBST(DB(PERIOD MAIN, 1, 10) contains "Rolling 12 Month", continue
numerator \ denominator

I'm just not 100% sure if I need to add an IF statement for the denominator to isolate "YTD" values.

Thank you,
Michael

Re: Rolling 12 Period over YTD Period Calculation

Posted: Wed Jul 28, 2021 9:51 am
by MarenC
Hi,
I'm just not 100% sure if I need to add an IF statement for the denominator to isolate "YTD" values.
I would say, probably.

You could try without and see what you get and then add in the relevant ifs to narrow to just the required elements.

Maren

Re: Rolling 12 Period over YTD Period Calculation

Posted: Wed Jul 28, 2021 1:31 pm
by michaelc99
MarenC wrote: ↑Wed Jul 28, 2021 9:51 am Hi,
I'm just not 100% sure if I need to add an IF statement for the denominator to isolate "YTD" values.
I would say, probably.

You could try without and see what you get and then add in the relevant ifs to narrow to just the required elements.

Maren
I added the code (below) which TM1 accepted from a syntax perspective, but I have to figure out where it's failing since the cube is not returning values for Turns (Gross)

Code: Select all

['Actual', 'Turns (Gross)'] = C:
IF(!PERIOD MAIN @= 'Jan '|SUBST(DB('GLOBAL VARIABLE','Current Year','String'),3,2)|' YTD Rolling 12-Month' % !PERIOD MAIN @= 'Feb '|SUBST(DB('GLOBAL VARIABLE','Current Year','String'),3,2)|' YTD Rolling 12-Month'
% !PERIOD MAIN @= 'Mar '|SUBST(DB('GLOBAL VARIABLE','Current Year','String'),3,2)|' YTD Rolling 12-Month' % !PERIOD MAIN @= 'Apr '|SUBST(DB('GLOBAL VARIABLE','Current Year','String'),3,2)|' YTD Rolling 12-Month'
% !PERIOD MAIN @= 'May '|SUBST(DB('GLOBAL VARIABLE','Current Year','String'),3,2)|' YTD Rolling 12-Month' % !PERIOD MAIN @= 'Jun '|SUBST(DB('GLOBAL VARIABLE','Current Year','String'),3,2)|' YTD Rolling 12-Month'
% !PERIOD MAIN @= 'Jul '|SUBST(DB('GLOBAL VARIABLE','Current Year','String'),3,2)|' YTD Rolling 12-Month' % !PERIOD MAIN @= 'Aug '|SUBST(DB('GLOBAL VARIABLE','Current Year','String'),3,2)|' YTD Rolling 12-Month'
% !PERIOD MAIN @= 'Sep '|SUBST(DB('GLOBAL VARIABLE','Current Year','String'),3,2)|' YTD Rolling 12-Month' % !PERIOD MAIN @= 'Oct '|SUBST(DB('GLOBAL VARIABLE','Current Year','String'),3,2)|' YTD Rolling 12-Month'
% !PERIOD MAIN @= 'Nov '|SUBST(DB('GLOBAL VARIABLE','Current Year','String'),3,2)|' YTD Rolling 12-Month' % !PERIOD MAIN @= 'Dec '|SUBST(DB('GLOBAL VARIABLE','Current Year','String'),3,2)|' YTD Rolling 12-Month',
['Actual', 'Total Departments', 'Cost of Goods Sold'] ,Continue) \

IF(!PERIOD MAIN @= 'Jan '|SUBST(DB('GLOBAL VARIABLE','Current Year','String'),3,2)|' YTD' % !PERIOD MAIN @= 'Feb '|SUBST(DB('GLOBAL VARIABLE','Current Year','String'),3,2)|' YTD'
% !PERIOD MAIN @= 'Mar '|SUBST(DB('GLOBAL VARIABLE','Current Year','String'),3,2)|' YTD' % !PERIOD MAIN @= 'Apr '|SUBST(DB('GLOBAL VARIABLE','Current Year','String'),3,2)|' YTD'
% !PERIOD MAIN @= 'May '|SUBST(DB('GLOBAL VARIABLE','Current Year','String'),3,2)|' YTD' % !PERIOD MAIN @= 'Jun '|SUBST(DB('GLOBAL VARIABLE','Current Year','String'),3,2)|' YTD'
% !PERIOD MAIN @= 'Jul '|SUBST(DB('GLOBAL VARIABLE','Current Year','String'),3,2)|' YTD' % !PERIOD MAIN @= 'Aug '|SUBST(DB('GLOBAL VARIABLE','Current Year','String'),3,2)|' YTD'
% !PERIOD MAIN @= 'Sep '|SUBST(DB('GLOBAL VARIABLE','Current Year','String'),3,2)|' YTD' % !PERIOD MAIN @= 'Oct '|SUBST(DB('GLOBAL VARIABLE','Current Year','String'),3,2)|' YTD'
% !PERIOD MAIN @= 'Nov '|SUBST(DB('GLOBAL VARIABLE','Current Year','String'),3,2)|' YTD' % !PERIOD MAIN @= 'Dec '|SUBST(DB('GLOBAL VARIABLE','Current Year','String'),3,2)|' YTD',
['Actual','Total Departments', 'Inventory-FIFO (Gross)'],Continue);
I have to think about the logic here as it doesn't feel right:

If(conditions=true, take COGS value, else continue) \ If(conditions=true, take Inventory-FIFO value, else continue)

Thank you,
Michael

Re: Rolling 12 Period over YTD Period Calculation

Posted: Wed Jul 28, 2021 3:20 pm
by declanr
michaelc99 wrote: ↑Tue Jul 27, 2021 1:50 am

Code: Select all

['Actual', 'Jan 21 YTD', 'Turns (Gross)'] = C:
['Actual', 'Total Departments', 'Jan 21 YTD Rolling 12-Month' , 'Cost of Goods Sold']  \  ['Actual','Total Departments','Jan 21 YTD', 'Inventory-FIFO (Gross)'];
I think if you go back to the above that worked for 1 month and try and expand that out a bit more.


First of all you need to include extra YTDs into the calculation.
I see 2 main options here.

Option 1:
Just specify the additional YTDs with direct text.

Code: Select all

['Actual', { 'Jan 21 YTD', 'Feb 21 YTD', 'Mar 21 YTD', 'Apr 21 YTD'...}, 'Turns (Gross)'] = C:
Pros - super easy to understand what is happening. Cons - You have to update it when there are new YTDs, if this is just once a year then maybe the pros significantly outweigh the cons.

Option 2:
Utilise an if statement on the Right hand side

Code: Select all

['Actual', 'Turns (Gross)'] = C:
If ( SubSt ( !Period Main, 8, 3 ) @= 'YTD' & Long ( !Period Main) = 10,
   # Do my Stuff,
   CONTINUE
);




Next we need to look at the actual calculation. You had it working for 1 month, and with the Left Hand side now only calculating for the YTDs you are interested in we just need to make it more dynamic.

Code: Select all

['Actual', 'Turns (Gross)'] = C:
If ( SubSt ( !Period Main, 8, 3 ) @= 'YTD' & Long ( !Period Main) = 10,
   (
      DB ( <CubeName>, !Version, 'Total Departments', !Period Main | '  Rolling 12-Month' , <Any other dimensions you have need to be listed>, 'Cost of Goods Sold'] 
      \  
      ['Total Departments', 'Inventory-FIFO (Gross)']
   ),
   CONTINUE
);
The denominator doesn't need to be a DB statement as you are not transforming any element names, you don't need to list the version or month dimension as you are using the exact same elements as the Left Hand Side for them.
The numerator however, needs to be a DB statement because you are doing string transformation to turn "Jan 21 YTD" into "Jan 21 YTD Rolling 12-Month". With a DB statement you need to make sure a reference to all dimensions in the cube is made (and in the correct order.)

I assumed that "Actual" relates to a dimension called something like version.


Edit - changed to also add a LONG to the Subst as I realised that the "...YTD Rolling 12-Month" elements also have "YTD" starting at the 8th character. You might need to find something here that ONLY targets the relevant YTD elements and depending what other element names you have it may get trickier.

Re: Rolling 12 Period over YTD Period Calculation

Posted: Thu Jul 29, 2021 2:10 pm
by michaelc99
declanr wrote: ↑Wed Jul 28, 2021 3:20 pm

Code: Select all

['Actual', 'Turns (Gross)'] = C:
If ( SubSt ( !Period Main, 8, 3 ) @= 'YTD' & Long ( !Period Main) = 10,
   (
      DB ( <CubeName>, !Version, 'Total Departments', !Period Main | '  Rolling 12-Month' , <Any other dimensions you have need to be listed>, 'Cost of Goods Sold'] 
      \  
      ['Total Departments', 'Inventory-FIFO (Gross)']
   ),
   CONTINUE
);
Good Morning Declanr,

I took your code above and updated the DB formula (see below) to match the structure of the Stats cube. After saving and looking at the results, the value of Turns (Gross) still returned blank for all periods.

Code: Select all

['Actual', 'Turns (Gross)'] = C:
If ( SubSt ( !Period Main, 8, 3 ) @= 'YTD' & Long ( !Period Main) = 10,
   (
      DB ( 'STATISTICS', !'VERSION MAIN', !'REGION MAIN', !'ENTITY MAIN', !'CURRENCY MAIN', 'Total Departments', !'COST CENTER MAIN', !'CHANNEL MAIN', !'PRODUCT CENTER MAIN', !'SITE MAIN', 'Cost of Goods Sold', !Period Main | ' Rolling 12-Month', !'GL MAIN MEASURE' )
       \  
      ['Total Departments', 'Inventory-FIFO (Gross)']),
   CONTINUE
);
So, I ended up going to the Account Stats dimension where I created two new parent level members: "Custom - Inventory Turns" (Level 2) > "Custom - Cost of Goods Sold RT" (Level 1). The Level 1 parent rolls up the members for COGS where, previously, I had a formula in the business rules (D = A+B+C) and a feeder that fed [A] >= [D], >= [D], [C] >= [D]. Once I cleaned up the dim and update the code (below) it worked as written/expected.

Code: Select all

['Actual', 'Turns (Gross)'] = C:
If ( SubSt ( !Period Main, 8, 3 ) @= 'YTD' & Long ( !Period Main) = 10,
   (
      DB ( 'STATISTICS', !'VERSION MAIN', !'REGION MAIN', !'ENTITY MAIN', !'CURRENCY MAIN', 'Total Departments', !'COST CENTER MAIN', !'CHANNEL MAIN', !'PRODUCT CENTER MAIN', !'SITE MAIN', 'Custom - Cost of Goods Sold RT', !Period Main | ' Rolling 12-Month', !'GL MAIN MEASURE' )
       \  
      ['Total Departments', 'Inventory-FIFO (Gross)']),
   CONTINUE
);
Thank you Declanr and MarenC for all of your help! I greatly appreciate it!

Thank you,
Michael
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited