Planning Analytics

------------------------------
Kleber Vieira
------------------------------

#PlanningAnalyticswithWatson

Original Message:
Sent: 9/22/2020 1:32:00 AM
From: Kleber Vieira
Subject: Moving balances from last month to next month



------------------------------
Kleber Vieira
------------------------------

#PlanningAnalyticswithWatson

Thanks Vivi, will give a try.

------------------------------
Kleber Vieira
------------------------------

Original Message:
Sent: Tue September 22, 2020 07:06 AM
From: Viviane Silvano Anselmo Dorsa
Subject: Moving balances from last month to next month

Hi Kleber,

Actually, we have moved the Closing Balance to Openning Balance  with rule at Openning Balance using Previous Month attribute (Month Dimension) to get the Closing Balance from previous period and avoid circular reference.

Then, we create a feeders rule where Closing Balance feed Openning Balance using Next Month attribute (Month Dimension). 

Don´t forget to check if Month = 'Jan', because you need to use 'Dez' from the previous year too and the same case to the feeders rule. 

I hope I´ve helped!

Viviane Dorsa
Solution Architect
	T:  +55 11 3578-0800 M: +55 11 97266-0300 viviane.dorsa@netpartners.com.br  Rua Alexandre Dumas, 1711 – Birmann 11 - 6º andar CEP: 04717-004 netpartners.com.br

"Este e-mail é direcionado/intencionado somente para os endereçados e pode conter informação privilegiada e confidencial. Não deve ser espalhada, distribuída e copiada. Se você recebeu este e-mail/mensagem por engano, por favor informe ao remetente e delete do seu sistema."

 "This e-mail is intended only for the addressee(s) and may contain privileged and confidential information. It should not be disseminated, distributed, or copied. If you have received this e-mail message by mistake, please inform the sender, and delete it from your system."

Original Message:
Sent: 9/22/2020 1:32:00 AM
From: Kleber Vieira
Subject: Moving balances from last month to next month



------------------------------
Kleber Vieira
------------------------------

#PlanningAnalyticswithWatson

Hi Kleber,
From the attachment I see you already have Opening Balance + Movements = Ending Balance. You can use rules in TM1 to copy/derive the Opening balance of the next month using the ending balance of previous month. 

Structure of the rule can be 

['Opening Balance'] = N: IF (!period @= Start Period), STET, DB ('Cube', ATTRS( 'Period', !Period, Period -1), others elements of the cube, Ending Balance));

Feeders may or may not be needed depending on your cube structure and measure dimension.

Define your area to be applied based on the version etc and the formulae can be set accordingly. ​
I am sure you have a period/month dimension where you can have attribute to define the period -1 i.e For Feb-20, Period -1 or Month-1 attribute is Jan and so on.
Also, we would need to STET the first month in the dimension or START period of your reporting.

You can also use TI's but rules are more preferable since they are dynamic.

------------------------------
Sanketh Suresh
------------------------------

Original Message:
Sent: Tue September 22, 2020 01:32 AM
From: Kleber Vieira
Subject: Moving balances from last month to next month



------------------------------
Kleber Vieira
------------------------------

#PlanningAnalyticswithWatson

Thanks San, will try it.

------------------------------
Kleber Vieira
------------------------------

Original Message:
Sent: Tue September 22, 2020 04:52 AM
From: Sanketh Suresh
Subject: Moving balances from last month to next month

Hi Kleber,
From the attachment I see you already have Opening Balance + Movements = Ending Balance. You can use rules in TM1 to copy/derive the Opening balance of the next month using the ending balance of previous month. 

Structure of the rule can be 

['Opening Balance'] = N: IF (!period @= Start Period), STET, DB ('Cube', ATTRS( 'Period', !Period, Period -1), others elements of the cube, Ending Balance));

Feeders may or may not be needed depending on your cube structure and measure dimension.

Define your area to be applied based on the version etc and the formulae can be set accordingly. ​
I am sure you have a period/month dimension where you can have attribute to define the period -1 i.e For Feb-20, Period -1 or Month-1 attribute is Jan and so on.
Also, we would need to STET the first month in the dimension or START period of your reporting.

You can also use TI's but rules are more preferable since they are dynamic.

------------------------------
Sanketh Suresh

Original Message:
Sent: Tue September 22, 2020 01:32 AM
From: Kleber Vieira
Subject: Moving balances from last month to next month



------------------------------
Kleber Vieira
------------------------------

#PlanningAnalyticswithWatson

Some words of caution seem appropriate:
a    Feeders will be required for almost any cube with production data
b    If your cube is a good size and/or you have many years to feed then you may find that your feeder stack fails and some numbers do not consolidate/zero suppress. To deal with this you need to introduce a 'break' whereby you copy (via TI) the closing balance into a (probably separate) opening balance element. This resets the stack and the feeders start working. I found that doing this mid year worked well.

------------------------------
David Usherwood
------------------------------

Original Message:
Sent: Tue September 22, 2020 08:10 PM
From: Kleber Vieira
Subject: Moving balances from last month to next month

Thanks San, will try it.

------------------------------
Kleber Vieira

Original Message:
Sent: Tue September 22, 2020 04:52 AM
From: Sanketh Suresh
Subject: Moving balances from last month to next month

Hi Kleber,
From the attachment I see you already have Opening Balance + Movements = Ending Balance. You can use rules in TM1 to copy/derive the Opening balance of the next month using the ending balance of previous month. 

Structure of the rule can be 

['Opening Balance'] = N: IF (!period @= Start Period), STET, DB ('Cube', ATTRS( 'Period', !Period, Period -1), others elements of the cube, Ending Balance));

Feeders may or may not be needed depending on your cube structure and measure dimension.

Define your area to be applied based on the version etc and the formulae can be set accordingly. ​
I am sure you have a period/month dimension where you can have attribute to define the period -1 i.e For Feb-20, Period -1 or Month-1 attribute is Jan and so on.
Also, we would need to STET the first month in the dimension or START period of your reporting.

You can also use TI's but rules are more preferable since they are dynamic.

------------------------------
Sanketh Suresh

Original Message:
Sent: Tue September 22, 2020 01:32 AM
From: Kleber Vieira
Subject: Moving balances from last month to next month



------------------------------
Kleber Vieira
------------------------------

#PlanningAnalyticswithWatson

Hi

I would not do this using feeders as it can get slow and as David has pointed out blow the stack. Instead I would suggest you try consolidation.

In your Period dimension generate consolidations like this

2020-M01_CTD
  Starting Balance
  2020-M01

2020-M02-CTD
  Starting Balance
  2020-M01
  2020-M02

Etc

(Do not be tempted to re-use 2020-M01_CTD in 2020-M02_CTD. This used to be a good idea, but in Planning Analytics it will create a very deep hierarchy by the time you get to eg 2025-M12 and that does horrible things to the way that Planning Analytics auto-generates MUNs - you don't need to know all the details - but it will cause memory issues, so you need to repeat the full list of Periods below each CTD consolidation).

In the measures you can have a consolidation

Total Movement
   Additions
   Withdrawals
   etc

Take the CTD consolidation at any Period crossed with Total Movement to get the closing balance. 

If you want to show the opening balance you can either just reference the CTD for the Period before or you can define a measure called Opening balance and define a rule to make this equal to the CTD of Total Movement from the Period before. The difference with this rule is that it only ever needs to look back one period. You are not creating a chain of feeders from one period to the next.

In general consolidation is 100 times faster than rules. This approach should give better performance and lower memory requirements.

Regards

Paul Simon


------------------------------
Paul Simon
------------------------------

Original Message:
Sent: Wed September 23, 2020 04:23 AM
From: David Usherwood
Subject: Moving balances from last month to next month

Some words of caution seem appropriate:
a    Feeders will be required for almost any cube with production data
b    If your cube is a good size and/or you have many years to feed then you may find that your feeder stack fails and some numbers do not consolidate/zero suppress. To deal with this you need to introduce a 'break' whereby you copy (via TI) the closing balance into a (probably separate) opening balance element. This resets the stack and the feeders start working. I found that doing this mid year worked well.

------------------------------
David Usherwood

Original Message:
Sent: Tue September 22, 2020 08:10 PM
From: Kleber Vieira
Subject: Moving balances from last month to next month

Thanks San, will try it.

------------------------------
Kleber Vieira

Original Message:
Sent: Tue September 22, 2020 04:52 AM
From: Sanketh Suresh
Subject: Moving balances from last month to next month

Hi Kleber,
From the attachment I see you already have Opening Balance + Movements = Ending Balance. You can use rules in TM1 to copy/derive the Opening balance of the next month using the ending balance of previous month. 

Structure of the rule can be 

['Opening Balance'] = N: IF (!period @= Start Period), STET, DB ('Cube', ATTRS( 'Period', !Period, Period -1), others elements of the cube, Ending Balance));

Feeders may or may not be needed depending on your cube structure and measure dimension.

Define your area to be applied based on the version etc and the formulae can be set accordingly. ​
I am sure you have a period/month dimension where you can have attribute to define the period -1 i.e For Feb-20, Period -1 or Month-1 attribute is Jan and so on.
Also, we would need to STET the first month in the dimension or START period of your reporting.

You can also use TI's but rules are more preferable since they are dynamic.

------------------------------
Sanketh Suresh

Original Message:
Sent: Tue September 22, 2020 01:32 AM
From: Kleber Vieira
Subject: Moving balances from last month to next month



------------------------------
Kleber Vieira
------------------------------

#PlanningAnalyticswithWatson

Kleber:

I agree with David: you can certainly use rules to pull the values forward from month to month, but there are a couple of reasons why using a TI to pull from closing balances in one year to opening balances of the next year is a better practice:

1) It gives you some flexibility and control over opening balances: making the "static" and not an active calculation means that you have better traceability and less risk of numbers mysteriously changing as a result of some attribute or assumption change. This is especially important in reporting applications especially for statutory reporting.

2) It gives you the option of adjusting opening balances: sometimes there are some mysterious year-end entries that need to get posted - and it provides an option.

3) You eliminate the risk of stack overflows, as others have explained.

Regards,
AG

------------------------------
Ann-Grete Tan
Chief Strategy & Marketing Officer
QueBIT Consulting

Analyze. Plan. Achieve.
https://quebit.com
------------------------------

Original Message:
Sent: Wed September 23, 2020 04:23 AM
From: David Usherwood
Subject: Moving balances from last month to next month

Some words of caution seem appropriate:
a    Feeders will be required for almost any cube with production data
b    If your cube is a good size and/or you have many years to feed then you may find that your feeder stack fails and some numbers do not consolidate/zero suppress. To deal with this you need to introduce a 'break' whereby you copy (via TI) the closing balance into a (probably separate) opening balance element. This resets the stack and the feeders start working. I found that doing this mid year worked well.

------------------------------
David Usherwood

Original Message:
Sent: Tue September 22, 2020 08:10 PM
From: Kleber Vieira
Subject: Moving balances from last month to next month

Thanks San, will try it.

------------------------------
Kleber Vieira

Original Message:
Sent: Tue September 22, 2020 04:52 AM
From: Sanketh Suresh
Subject: Moving balances from last month to next month

Hi Kleber,
From the attachment I see you already have Opening Balance + Movements = Ending Balance. You can use rules in TM1 to copy/derive the Opening balance of the next month using the ending balance of previous month. 

Structure of the rule can be 

['Opening Balance'] = N: IF (!period @= Start Period), STET, DB ('Cube', ATTRS( 'Period', !Period, Period -1), others elements of the cube, Ending Balance));

Feeders may or may not be needed depending on your cube structure and measure dimension.

Define your area to be applied based on the version etc and the formulae can be set accordingly. ​
I am sure you have a period/month dimension where you can have attribute to define the period -1 i.e For Feb-20, Period -1 or Month-1 attribute is Jan and so on.
Also, we would need to STET the first month in the dimension or START period of your reporting.

You can also use TI's but rules are more preferable since they are dynamic.

------------------------------
Sanketh Suresh

Original Message:
Sent: Tue September 22, 2020 01:32 AM
From: Kleber Vieira
Subject: Moving balances from last month to next month



------------------------------
Kleber Vieira
------------------------------

#PlanningAnalyticswithWatson

Hi Kleber,

We have a blog post that answers this exact question. If you head over to https://exploringtm1.com/how-to-roll-forward-values-from-one-period-to-the-next-in-tm1/, you will find a fully worked example, with the rules and feeders to make it all work and a downloadable example model.

Give me a yell if you need anything explained.

Cheers,

John

------------------------------
John Vaughan
------------------------------

Original Message:
Sent: Tue September 22, 2020 01:32 AM
From: Kleber Vieira
Subject: Moving balances from last month to next month



------------------------------
Kleber Vieira
------------------------------

#PlanningAnalyticswithWatson