TM1 Forum

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

YTD TO MTD Conversion using TI

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

Page 1 of 1

YTD TO MTD Conversion using TI

Posted: Thu Sep 22, 2011 8:48 pm
by ravi.ahuja21
Hi Folks,
I have a cube named 'IT Others' having these many dimension
  • Function,
    Department
    Central Nature,
    Serial No
    Scenario
    Facility
    Year
    Months(It is having both MTD and YTD Elements, I have used the YTD Elements as consolidation containing MTD months For Eg. YTD Feb contains Jan & Feb)
    IT Measures
I am trying to do this in the epilog tab but I am getting wrong values populated in the cube.

Also if the method follow above for YTD and MTD conversion is correct or should i have an extra dimension as View having YTD and MTD and then i write rules for them to convert it into YTD.

Please give me your valuable inputs.

Code: Select all

Epilog Tab.
vTempAmt=0;
vAmtJan=CellGetN('IT Others',vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'Jan','Amount LC');
vAmtFeb=CellGetN('IT Others',vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'Feb','Amount LC');


vTempAmt=vAmtFeb-vAmtJan;
#ASCIIOUTPUT('OTHERS.cma' ,NumberToString(vTempAmt),vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'Feb');

CellPutN(vTempAmt,'IT Others',vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'Feb','Amount LC');


vTempAmt=0;



vAmtMar=CellGetN('IT Others',vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'Mar','Amount LC');

vTempAmt=vAmtMar-vAmtFeb;

ASCIIOUTPUT('OTHERS.cma' ,NumberToString(vTempAmt),vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'Mar');
CellPutN(vTempAmt,'IT Others',vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'Mar','Amount LC');

vTempAmt=0;


vAmtApr=CellGetN('IT Others',vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'Apr','Amount LC');

vTempAmt=vAmtApr-vAmtMar;

CellPutN(vTempAmt,'IT Others',vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'Apr','Amount LC');
ASCIIOUTPUT('OTHERS.cma' ,NumberToString(vTempAmt),vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'Apr');
vTempAmt=0;



vAmtMay=CellGetN('IT Others',vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'May','Amount LC');

vTempAmt=vAmtMay-vAmtApr;

CellPutN(vTempAmt,'IT Others',vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'May','Amount LC');
ASCIIOUTPUT('OTHERS.cma' ,NumberToString(vTempAmt),vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'May');
vTempAmt=0;


vAmtJun=CellGetN('IT Others',vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'Jun','Amount LC');
vTempAmt=vAmtJun-vAmtMay;

CellPutN(vTempAmt,'IT Others',vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'Jun','Amount LC');

vTempAmt=0;


vAmtJuly=CellGetN('IT Others',vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'July','Amount LC');
vTempAmt=vAmtJuly-vAmtJun;

CellPutN(vTempAmt,'IT Others',vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'July','Amount LC');

vTempAmt=0;



vAmtAug=CellGetN('IT Others',vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'Aug','Amount LC');
vTempAmt=vAmtAug-vAmtJuly;

CellPutN(vTempAmt,'IT Others',vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'Aug','Amount LC');
vTempAmt=0;



vAmtSep=CellGetN('IT Others',vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'Sep','Amount LC');
vTempAmt=vAmtSep-vAmtAug;

CellPutN(vTempAmt,'IT Others',vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'Sep','Amount LC');
vTempAmt=0;

vAmtOct=CellGetN('IT Others',vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'Oct','Amount LC');

vTempAmt=vAmtOct-vAmtSep;

CellPutN(vTempAmt,'IT Others',vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'Oct','Amount LC');
vTempAmt=0;

vAmtNov=CellGetN('IT Others',vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'Nov','Amount LC');
vTempAmt=vAmtNov-vAmtOct;

CellPutN(vTempAmt,'IT Others',vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'Nov','Amount LC');
vTempAmt=0;

vAmtDec=CellGetN('IT Others',vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'Dec','Amount LC');
vTempAmt=vAmtDec-vAmtNov;

CellPutN(vTempAmt,'IT Others',vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,'Dec','Amount LC');
vTempAmt=0;

Re: YTD TO MTD Conversion using TI

Posted: Fri Sep 23, 2011 9:56 am
by qml
ravi.ahuja21 wrote:I am trying to do this in the epilog tab but I am getting wrong values populated in the cube.
Erm... what do you mean by "this"?
And if, by any chance, you require any help, then can you point me to the question in your post? I don't seem to be able to find it.

Re: YTD TO MTD Conversion using TI

Posted: Fri Sep 23, 2011 12:30 pm
by ravi.ahuja21
I mean to say, I am getting values which I dont expect to be in the cube, is the code given above correct can you do this kind of a conversion.?

Re: YTD TO MTD Conversion using TI

Posted: Fri Sep 23, 2011 12:44 pm
by qml
I don't think anyone will be able to fully help you until you specify what you are trying to achieve and show how you are trying to achieve it (the contents of the lone Epilog tab does not qualify in my opinion).

My guess is that your first and foremost issue is with placing the code in the Epilog tab instead of the Data tab. But then again I don't know what your data source is, how your variables are defined and what you're trying to do.

Re: YTD TO MTD Conversion using TI

Posted: Fri Sep 23, 2011 7:08 pm
by ravi.ahuja21
Ok Sorry. Got u.I will help u with that.

I have got the attachments for you.

Basically I am calculating MTD values from YTD ones
For EG;
Product Month YTD Amt MTD Amt (OUTPUT)
1 Jan 10 10
2 Feb 15 5
3 Mar 20 5



Hope this makes it more clear.

Re: YTD TO MTD Conversion using TI

Posted: Sun Sep 25, 2011 11:52 am
by qml
Assuming your vFunction, vDept, vNature, vSerialNo, vScenario, vFacility, and vYear variables are defined on the Variables tab based on your data source, then this code should work fine, provided you put it in the Data tab, not Epilog, like I already suggested, and your data source contains only Dec YTD numbers. The Data tab is executed once per every row of source data (which is what you want), Epilog only once at the end (not what you want).

If, however, your data source contains all months in reverse order (and additionally Jan-Dec are consecutive elements in the Month dimension), then your Data tab could look something like this to achieve the result you want:

Code: Select all

IF(vMonth @= 'Jan');
  ITEMSKIP;
ELSE;
  CellPutN(
# take YTD value and subtract the previous month
    Value - CellGetN('IT Others',vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,
# in the below line we take the previous month
    DIMNM('Month',DIMIX('Month',vMonth)-1),
    'Amount LC'),'IT Others',vFunction,vDept,vNature,vSerialNo,vScenario,vFacility,vYear,vMonth,'Amount LC'
  );
ENDIF;

Re: YTD TO MTD Conversion using TI

Posted: Mon Sep 26, 2011 7:48 pm
by ravi.ahuja21
Thanks for the valuable time and input.That solves the problem.

Re: YTD TO MTD Conversion using TI

Posted: Thu Aug 15, 2013 1:08 am
by jktm1
Dear TM1 gurus,

I am very new to TM1 and also very new to this site. I found this site very useful and greatful to all of you guys assisting TM1 newbees - hopefully you can help me as well.

I have not yet figure out a way to create a new post so I am hooking my query to this post... even though it's quite old post, very similar to what i am trying to achieve so hopefully you guys can find this and point me to the right direction. I am trying to work out period number from YTD data source in TI process. I can only get it working only when a product appears in data source... but if a particular product doesn't appear in YTD data source but there was a balance in prior period YTD then it doesn't calculate correctly.

For example
in TM1, currently following data exists;
JunYTD, Product1, 100
JunYTD, Product2, 300
JunYTD, Product3, 50

and in data source, following data exists;
JulYTD, Product2, 500
JulYTD, Product3, -100
JulYTD, Product4, 50

I am expecting to achieve results as follows;
Jul, Product1, -100
Jul, Product2, 200
Jul, Product3, -150
Jul, Product4, 50

I tried to substract CellGetN of prior period YTD values in TM1 from current period YTD values in data source file and load them using CellPutN but since Product1 doesn't exist in data source file, it shows zero for Product1 in TM1.

CellPutN=(Value, sCube, vYear, vPeriod, vCurrency, vScenario, vEntity, vProduct, vMeasure) - CellGetN(sCube,vYear, ATTRS('dPeriod',vPeriod,'PriorYTD'), vCurrency, vScenario, vEntity, vProduct, vMeasure)

Is there a way of capture Product1? Do I have to ensure that data source file has ALL products (even value is zero)? Any suggestions would be appreciated.

Thanks!

Re: YTD TO MTD Conversion using TI

Posted: Thu Aug 15, 2013 12:01 pm
by tomok
jktm1 wrote: For example
in TM1, currently following data exists;
JunYTD, Product1, 100
JunYTD, Product2, 300
JunYTD, Product3, 50

and in data source, following data exists;
JulYTD, Product2, 500
JulYTD, Product3, -100
JulYTD, Product4, 50

I am expecting to achieve results as follows;
Jul, Product1, -100
Jul, Product2, 200
Jul, Product3, -150
Jul, Product4, 50

I tried to substract CellGetN of prior period YTD values in TM1 from current period YTD values in data source file and load them using CellPutN but since Product1 doesn't exist in data source file, it shows zero for Product1 in TM1.
Is there a way of capture Product1? Do I have to ensure that data source file has ALL products (even value is zero)? Any suggestions would be appreciated.
The problem you are having is because your data source does not have a record for Product1 so when you loop through the records (on the Data tab), no action is going to to be taken on Product1. All this particular TI knows about is the Products in the source file. What you need to do is create two processes. One that uses a View of the cube, pulling the prior YTD values for all products, and then the second being your source file. Here is the logic:

TI 1(using the View) - Multiply the balance from the prior YTD times -1 and send that to the current YTD.
TI2 (using the existing file - Add the value in the file to the current YTD (using the CellGetN / CellPutN logic).

Re: YTD TO MTD Conversion using TI

Posted: Tue Aug 20, 2013 2:17 am
by jktm1
Hi tomok,

Thanks for your input. I got it working!
All times are UTC
Page 1 of 1

Powered by phpBB® Forum Software © phpBB Limited