Planning Analytics

Hi All,

Is it possible to load data at consolidated element? Tried with Xmonth = 'Total' but it prompt error consolidated type.
When I changed XMonth = 'Jan' it loaded successfully to Jan.

In my case I wanted to load Amount to "Total" which refers to (Jan-Dec).
Understood that additional code must be written after the generated section. Appreciate if anyone can help me with the code. Tried google on this topic but have not found any solution yet.


#****Begin: Generated Statements***
Version='Version 1';
XMonth='Jan';
CellPutN(Amount,'ME RD Reallocation',Version,Entity,ME,RC,XMonth);
#****End: Generated Statements****

Thank you in advanced.

regards
Ida

------------------------------
ida lasim
------------------------------

#PlanningAnalyticswithWatson

Hi Ida,

Do you want to spread on months? If yes, you can use data spreading through TI : https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=cmtf-cellputproportionalspread

Regards
Cécile

------------------------------
DESCHARD Cécile
------------------------------

Original Message:
Sent: Mon December 20, 2021 09:12 PM
From: ida lasim
Subject: Load Data to Consolidated element in TI

Hi All,

Is it possible to load data at consolidated element? Tried with Xmonth = 'Total' but it prompt error consolidated type.
When I changed XMonth = 'Jan' it loaded successfully to Jan.

In my case I wanted to load Amount to "Total" which refers to (Jan-Dec).
Understood that additional code must be written after the generated section. Appreciate if anyone can help me with the code. Tried google on this topic but have not found any solution yet.


#****Begin: Generated Statements***
Version='Version 1';
XMonth='Jan';
CellPutN(Amount,'ME RD Reallocation',Version,Entity,ME,RC,XMonth);
#****End: Generated Statements****

Thank you in advanced.

regards
Ida

------------------------------
ida lasim
------------------------------

#PlanningAnalyticswithWatson

HI Cécile,

Thanks for your reply. However, I wanted to load 56k lines into the cube with Amount column mapped to Total which later to be spread.


Thanks!

------------------------------
ida lasim
------------------------------

Original Message:
Sent: Tue December 21, 2021 02:58 AM
From: DESCHARD Cécile
Subject: Load Data to Consolidated element in TI

Hi Ida,

Do you want to spread on months? If yes, you can use data spreading through TI : https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=cmtf-cellputproportionalspread

Regards
Cécile

------------------------------
DESCHARD Cécile

Original Message:
Sent: Mon December 20, 2021 09:12 PM
From: ida lasim
Subject: Load Data to Consolidated element in TI

Hi All,

Is it possible to load data at consolidated element? Tried with Xmonth = 'Total' but it prompt error consolidated type.
When I changed XMonth = 'Jan' it loaded successfully to Jan.

In my case I wanted to load Amount to "Total" which refers to (Jan-Dec).
Understood that additional code must be written after the generated section. Appreciate if anyone can help me with the code. Tried google on this topic but have not found any solution yet.


#****Begin: Generated Statements***
Version='Version 1';
XMonth='Jan';
CellPutN(Amount,'ME RD Reallocation',Version,Entity,ME,RC,XMonth);
#****End: Generated Statements****

Thank you in advanced.

regards
Ida

------------------------------
ida lasim
------------------------------

#PlanningAnalyticswithWatson

You cannot load data on a consolidation element (data is stored at a leaf level). You could had a no month element under the Total and then spread it.

------------------------------
DESCHARD Cécile
------------------------------

Original Message:
Sent: Tue December 21, 2021 04:17 AM
From: ida lasim
Subject: Load Data to Consolidated element in TI

HI Cécile,

Thanks for your reply. However, I wanted to load 56k lines into the cube with Amount column mapped to Total which later to be spread.


Thanks!

------------------------------
ida lasim

Original Message:
Sent: Tue December 21, 2021 02:58 AM
From: DESCHARD Cécile
Subject: Load Data to Consolidated element in TI

Hi Ida,

Do you want to spread on months? If yes, you can use data spreading through TI : https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=cmtf-cellputproportionalspread

Regards
Cécile

------------------------------
DESCHARD Cécile

Original Message:
Sent: Mon December 20, 2021 09:12 PM
From: ida lasim
Subject: Load Data to Consolidated element in TI

Hi All,

Is it possible to load data at consolidated element? Tried with Xmonth = 'Total' but it prompt error consolidated type.
When I changed XMonth = 'Jan' it loaded successfully to Jan.

In my case I wanted to load Amount to "Total" which refers to (Jan-Dec).
Understood that additional code must be written after the generated section. Appreciate if anyone can help me with the code. Tried google on this topic but have not found any solution yet.


#****Begin: Generated Statements***
Version='Version 1';
XMonth='Jan';
CellPutN(Amount,'ME RD Reallocation',Version,Entity,ME,RC,XMonth);
#****End: Generated Statements****

Thank you in advanced.

regards
Ida

------------------------------
ida lasim
------------------------------

#PlanningAnalyticswithWatson

There is a config option for "allow separate N and C rules."  Enabling it may help you get past your first hump of loading to a C element.  If you're going through PAW, it is in Config-->Modeling-->Rules.

What is the basis for the spread to the lower level?  If this is a one-time exercise, it may be easier to do your math in excel than DBS/DBSW the values into the N level elements.   If it's an ongoing need, I'll defer to the experts in writing TIs - it sounds like creating then destroying a helper object may be a good approach.

------------------------------
Jessica Quiles
------------------------------

Original Message:
Sent: Tue December 21, 2021 08:38 AM
From: DESCHARD Cécile
Subject: Load Data to Consolidated element in TI

You cannot load data on a consolidation element (data is stored at a leaf level). You could had a no month element under the Total and then spread it.

------------------------------
DESCHARD Cécile

Original Message:
Sent: Tue December 21, 2021 04:17 AM
From: ida lasim
Subject: Load Data to Consolidated element in TI

HI Cécile,

Thanks for your reply. However, I wanted to load 56k lines into the cube with Amount column mapped to Total which later to be spread.


Thanks!

------------------------------
ida lasim

Original Message:
Sent: Tue December 21, 2021 02:58 AM
From: DESCHARD Cécile
Subject: Load Data to Consolidated element in TI

Hi Ida,

Do you want to spread on months? If yes, you can use data spreading through TI : https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=cmtf-cellputproportionalspread

Regards
Cécile

------------------------------
DESCHARD Cécile

Original Message:
Sent: Mon December 20, 2021 09:12 PM
From: ida lasim
Subject: Load Data to Consolidated element in TI

Hi All,

Is it possible to load data at consolidated element? Tried with Xmonth = 'Total' but it prompt error consolidated type.
When I changed XMonth = 'Jan' it loaded successfully to Jan.

In my case I wanted to load Amount to "Total" which refers to (Jan-Dec).
Understood that additional code must be written after the generated section. Appreciate if anyone can help me with the code. Tried google on this topic but have not found any solution yet.


#****Begin: Generated Statements***
Version='Version 1';
XMonth='Jan';
CellPutN(Amount,'ME RD Reallocation',Version,Entity,ME,RC,XMonth);
#****End: Generated Statements****

Thank you in advanced.

regards
Ida

------------------------------
ida lasim
------------------------------

#PlanningAnalyticswithWatson

"Allow separate N and C rules" is not to change how data is stored but at what levels you can apply rules i.e. N:, C: or not specified for both.

You cannot write to a C level unless it is a string, full stop.
You would need to load into another element e.g. # Full Year then break it back from there - you could use spreading, rules, manual capture etc.

If you think of a C level element in Excel terms, the C level cell has a SUM() formula in it. It sums up the relevant cells. You cannot enter the SUM() formula and values into the same cell, it automatically aggregates unless you apply a rule to that specific C level intersection.

Hope that clears things up a bit better.
​

------------------------------
George Tonkin
------------------------------

Original Message:
Sent: Wed December 22, 2021 04:18 PM
From: Jessica Quiles
Subject: Load Data to Consolidated element in TI

There is a config option for "allow separate N and C rules."  Enabling it may help you get past your first hump of loading to a C element.  If you're going through PAW, it is in Config-->Modeling-->Rules.

What is the basis for the spread to the lower level?  If this is a one-time exercise, it may be easier to do your math in excel than DBS/DBSW the values into the N level elements.   If it's an ongoing need, I'll defer to the experts in writing TIs - it sounds like creating then destroying a helper object may be a good approach.

------------------------------
Jessica Quiles

Original Message:
Sent: Tue December 21, 2021 08:38 AM
From: DESCHARD Cécile
Subject: Load Data to Consolidated element in TI

You cannot load data on a consolidation element (data is stored at a leaf level). You could had a no month element under the Total and then spread it.

------------------------------
DESCHARD Cécile

Original Message:
Sent: Tue December 21, 2021 04:17 AM
From: ida lasim
Subject: Load Data to Consolidated element in TI

HI Cécile,

Thanks for your reply. However, I wanted to load 56k lines into the cube with Amount column mapped to Total which later to be spread.


Thanks!

------------------------------
ida lasim

Original Message:
Sent: Tue December 21, 2021 02:58 AM
From: DESCHARD Cécile
Subject: Load Data to Consolidated element in TI

Hi Ida,

Do you want to spread on months? If yes, you can use data spreading through TI : https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=cmtf-cellputproportionalspread

Regards
Cécile

------------------------------
DESCHARD Cécile

Original Message:
Sent: Mon December 20, 2021 09:12 PM
From: ida lasim
Subject: Load Data to Consolidated element in TI

Hi All,

Is it possible to load data at consolidated element? Tried with Xmonth = 'Total' but it prompt error consolidated type.
When I changed XMonth = 'Jan' it loaded successfully to Jan.

In my case I wanted to load Amount to "Total" which refers to (Jan-Dec).
Understood that additional code must be written after the generated section. Appreciate if anyone can help me with the code. Tried google on this topic but have not found any solution yet.


#****Begin: Generated Statements***
Version='Version 1';
XMonth='Jan';
CellPutN(Amount,'ME RD Reallocation',Version,Entity,ME,RC,XMonth);
#****End: Generated Statements****

Thank you in advanced.

regards
Ida

------------------------------
ida lasim
------------------------------

#PlanningAnalyticswithWatson

I was thinking something along the lines of what is noted in this article:

https://exploringtm1.com/feeding-from-a-consolidation-to-a-leaf-element-in-tm1/



------------------------------
Jessica Quiles
------------------------------

Original Message:
Sent: Thu December 23, 2021 09:57 AM
From: George Tonkin
Subject: Load Data to Consolidated element in TI

"Allow separate N and C rules" is not to change how data is stored but at what levels you can apply rules i.e. N:, C: or not specified for both.

You cannot write to a C level unless it is a string, full stop.
You would need to load into another element e.g. # Full Year then break it back from there - you could use spreading, rules, manual capture etc.

If you think of a C level element in Excel terms, the C level cell has a SUM() formula in it. It sums up the relevant cells. You cannot enter the SUM() formula and values into the same cell, it automatically aggregates unless you apply a rule to that specific C level intersection.

Hope that clears things up a bit better.
​

------------------------------
George Tonkin

Original Message:
Sent: Wed December 22, 2021 04:18 PM
From: Jessica Quiles
Subject: Load Data to Consolidated element in TI

There is a config option for "allow separate N and C rules."  Enabling it may help you get past your first hump of loading to a C element.  If you're going through PAW, it is in Config-->Modeling-->Rules.

What is the basis for the spread to the lower level?  If this is a one-time exercise, it may be easier to do your math in excel than DBS/DBSW the values into the N level elements.   If it's an ongoing need, I'll defer to the experts in writing TIs - it sounds like creating then destroying a helper object may be a good approach.

------------------------------
Jessica Quiles

Original Message:
Sent: Tue December 21, 2021 08:38 AM
From: DESCHARD Cécile
Subject: Load Data to Consolidated element in TI

You cannot load data on a consolidation element (data is stored at a leaf level). You could had a no month element under the Total and then spread it.

------------------------------
DESCHARD Cécile

Original Message:
Sent: Tue December 21, 2021 04:17 AM
From: ida lasim
Subject: Load Data to Consolidated element in TI

HI Cécile,

Thanks for your reply. However, I wanted to load 56k lines into the cube with Amount column mapped to Total which later to be spread.


Thanks!

------------------------------
ida lasim

Original Message:
Sent: Tue December 21, 2021 02:58 AM
From: DESCHARD Cécile
Subject: Load Data to Consolidated element in TI

Hi Ida,

Do you want to spread on months? If yes, you can use data spreading through TI : https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=cmtf-cellputproportionalspread

Regards
Cécile

------------------------------
DESCHARD Cécile

Original Message:
Sent: Mon December 20, 2021 09:12 PM
From: ida lasim
Subject: Load Data to Consolidated element in TI

Hi All,

Is it possible to load data at consolidated element? Tried with Xmonth = 'Total' but it prompt error consolidated type.
When I changed XMonth = 'Jan' it loaded successfully to Jan.

In my case I wanted to load Amount to "Total" which refers to (Jan-Dec).
Understood that additional code must be written after the generated section. Appreciate if anyone can help me with the code. Tried google on this topic but have not found any solution yet.


#****Begin: Generated Statements***
Version='Version 1';
XMonth='Jan';
CellPutN(Amount,'ME RD Reallocation',Version,Entity,ME,RC,XMonth);
#****End: Generated Statements****

Thank you in advanced.

regards
Ida

------------------------------
ida lasim
------------------------------

#PlanningAnalyticswithWatson