TM1 should display values in /1000 format.

[Jlekkala]

Hello Everyone,

I need some help related to data formatting.
We re loading the data values form SQL, and data load is working as excepted.
Now user wants to see the values in TM1 in /1000 format.
Ex - of the cube view data is displaying as 4,880,505 - user wants to see value as /1000.
4880505 /1000 = 4881
I have updated the TI process with the below line of code
nValue1= nAmount1\1000; and by using cellputn i am loading the nValue into cube.
but the problem i am facing is, when i am adding that code, values are not matching, it seems may be because of dimension consolidation. i am attaching the dimension structure.
but with normal load values are matching.

please give me some idea.
Any custom format we can use? if so please suggest.
Thank you in advance.

--------------------------------------------------------

asciioutput('E:\TM1Data\CE DM\Extracts\financial'| vAdjType |''|vPostingPeriod|'.csv', vItem, vPostingLevel, vConsolidationUnit, vFunctionalArea, vProfitCenter, vFinDataVersion, vBrandFamily
, vProductType, vPLLine, vAdjustmentType, vPostingPeriod,NumberToString(vAmount),NumberToString(vAmount1));


vItem=SUBST(vItem,4,5);
vAccount='In'| vItem;
vConsolUnit=SUBST(vConsolidationUnit,4,4);
vFunctArea=vFunctionalArea;
vSKUMarket='In'|SUBST(vProfitCenter,6,5);
vBrand = vBrandFamily;
vAdjType=IF(StringToNumber(vAdjustmentType)<10,'000'|vAdjustmentType,'00'|vAdjustmentType);
vProfitCenter1=SUBST(vProfitCenter,6,5);
vProfitCenter = TRIM(vProfitCenter1);
vPostingPeriod=IF(StringToNumber(vPostingPeriod)<10,'M0'|vPostingPeriod,'M'|vPostingPeriod);
vValue=vAmount;
vValue1=vAmount1;

vMarket = ELPAR('CE DM SKU Market PLAN', vProfitCenter,1);


IF( StringToNumber( vPostingLevel ) < 10 );
vPostingLevel = '0' | vPostingLevel;
ENDIF;

IF(vPLLine @= 'PRIMARY PRODUCION COST');
vPLLine = 'PRIMARY PRODUCTION COST';
ENDIF;


IF( vValue = 0 & vValue1=0 );
ItemSkip;
ENDIF;

IF(LONG(vProfitCenter)>4);
ITEMSKIP;
ENDIF;

IF(DIMIX('CE DM P&L Account', vAccount) = 0);
ITEMSKIP;
ENDIF;


vPCBrand = vProfitCenter | ' ' | vBrand;
vInPCBrand = 'In'|vPCBrand;

ATTRPUTS( vProfitCenter, 'CE DM Profit Center', vPCBrand, 'Market Parent');


ATTRPUTS( vBrand, 'CE DM Profit Center', vPCBrand, 'Brand Parent');

sBrandWithSameParent = vBrand | vBrand;

IF(DIMIX('CE DM Profit Center', sBrandWithSameParent)>0);
ATTRPUTS( sBrandWithSameParent, 'CE DM Profit Center', vPCBrand, 'Brand Parent');
ENDIF;

ATTRPUTN( 1, 'CE DM SKU Market', vInPCBrand, 'PCBrandDummy');


#=====================
# Define Cube Dimensions
#=====================

vDim01=pScenarioTarget;
vDim02=vPostingPeriod;
vDim03=vAdjType;
vDim04=vPostingLevel;
vDim05=ATTRS('CE DM Scenario',pScenarioTarget,'FXRateType');
vDim06=vConsolUnit;
vDim07='SDxx';
#vDim08=vSKUMarket;
vDim08=vInPCBrand;
vDim09=vPCBrand;
vDim10='CC00000';
vDim11='InternalOrder01';
vDim12=vFunctArea;
vDim13=vAccount;
vDim14=vPLLine;
vDim15='BaseValue';

#=====================
# Update attributes
#=====================


AttrPutS(vPCBrand, 'CE DM SKU Market PLAN', vInPCBrand, 'Level1_BrandStyle');
AttrPutS(vProfitCenter, 'CE DM SKU Market PLAN',vInPCBrand, 'Level2_ProfitCenter');
AttrPutS(vMarket, 'CE DM SKU Market PLAN', vInPCBrand, 'Level3_Market');
AttrPutS('PC-MAR', 'CE DM SKU Market PLAN', vInPCBrand, 'Level5_MarketTotal');


##=====================
# Load Amount
#=====================

nOldAmount = CELLGETN(vCube,vDim01,vDim02,vDim03,vDim04,vDim05,vDim06,vDim07,vDim08,vDim09,vDim10,vDim11,vDim12,vDim13,vDim14,vDim15);
#noldamount = noldamount\1000;
asciioutput('E:\TM1Data\CE DM\Logfiles\cedm_noldamount.csv',numbertostring(noldamount),numbertostring(vValue),vCube,vDim01,vDim02,vDim03,vDim04,vDim05,vDim06,vDim07,vDim08,vDim09,vDim10,vDim11,vDim12,vDim13,vDim14,vDim15);


IF(vValue<>0);

#vValue=vValue\1000;
nAmount = nOldAmount + vValue;
## conversion to /1000
#nValue = nAmount\1000;

#asciioutput('E:\TM1Data\CE DM\Logfiles\cedm_actualsifNOT0'|sPromptName4|''|vPostingPeriod|'.csv',numbertostring(vValue),numbertostring(nValue),sPromptName0|sPromptName3,vCube,vDim01,vDim02,vDim03,vDim04,vDim05,vDim06,vDim07,vDim08,vDim09,vDim10,vDim11,vDim12,vDim13,vDim14,vDim15);
CELLPUTN(nValue ,vCube,vDim01,vDim02,vDim03,vDim04,vDim05,vDim06,vDim07,vDim08,vDim09,vDim10,vDim11,vDim12,vDim13,vDim14,vDim15);
ENDIF;

IF( vDim01 @= '2023 Actuals BW @AP23');
nOldAmount = CELLGETN(vCube,vDim01,vDim02,vDim03,vDim04,vDim05,vDim06,vDim07,vDim08,vDim09,vDim10,vDim11,vDim12,vDim13,vDim14,vDim15);
nAmount1 = nOldAmount + vValue1;
## conversion to /1000
#nValue1= nAmount1\1000;
asciioutput('E:\TM1Data\CE DM\Logfiles\@23cedm_actualsifNOT0'|sPromptName4|'.csv',numbertostring(vValue1),sPromptName0|sPromptName3,vCube,vDim01,vDim02,vDim03,vDim04,vDim05,vDim06,vDim07,vDim08,vDim09,vDim10,vDim11,vDim12,vDim13,vDim14,vDim15);
CELLPUTN(nValue1,vCube,vDim01,vDim02,vDim03,vDim04,vDim05,vDim06,vDim07,vDim08,vDim09,vDim10,vDim11,vDim12,vDim13,vDim14,vDim15);
ENDIF;

[ascheevel]

Have you considered loading the data as is from source and using formatting in TM1 to display millions as thousands?

Thousands: #,##0,;(#,##0,)
Millions: #,##0,,;(#,##0,,)

[MarenC]

Hi,

I have used the formatting suggested by ascheevel and it works well in PAW, but the main problem with this is that for the excel reports it won't use the server formatting unless you select the option to do this, and then you might not want that.

The other option is to create a rule for the Thousands measure

Maren

[Wim Gielis]

Or create a new measure with the old measure as a child.
The weight is 0.001 for thousands.
No need for rules and you are not loading your values twice.
Maybe what is needed is a bit of formatting to not show decimals.

[Jlekkala]

Thank you for suggestions.
I edited the element weight of base measure to 0.001 and it worked.
User is checking the consolidations, and is happy that its displaying as excepted.
Thank you for support.

Best Regards,
Jagadish

[Jlekkala]

I have one doubt,
is there any chance it applies to N level also.
As of now its applying only for C level element.
Please suggest

[Wim Gielis]

By adding a child you indeed convert to a C element.
What is the requirement for keeping it as an n-element ?

[Jlekkala]

Yes, /1000 fromat is applied to C level element
but for N level, its not showing /1000 value.
Please suggest

[Wim Gielis]

How can the n level show the value /1000 ?
It is the big value that is divided by 1000 and shown on the C level. The n level is unchanged.

[Jlekkala]

Got it Wim.
Thank you for support.

Regards,
Jagadish