Page 1 of 1
TI Process to filter subset on the basis of actual month
Posted: Thu Jul 10, 2014 4:10 pm
by dharav
Hello All
I would like to know about the TI Process, where i can filter month dimension subset-Forecast Month on the basis of actual month in control cube.
Suppose in my control cube, actual month is Jun - Now i want month dimension subset to filter all elements after may. (Jun to Dec should remain in subset)
I tried out as per following
Smonth=Cellgets('1f_admin','Actual Month','Version - Month View');
i=1;
while (i<13);
IF(i<DIMIX('1t_MONTHs',SMONTH));
SubsetElementDelete('1t_months','Forecastmonth',i);
Endif;
i=i+1;
End;
The result of process was (Feb,apr,jun,aug,oct,dec); It skip one element and delete one element. smonth=Jun and my expected result (May to dec)
What could be the reason affecting the result and is there better way to implement the process? May do it on the basis of attribute also?
Thanks
Dharav
Re: TI Process to filter subset on the basis of actual month
Posted: Thu Jul 10, 2014 4:22 pm
by jim wood
You could create a control cube with 2 dimensions. One with the numbers 1-12, the other with a single text element. Then enter in to the cube:
1 Jan
2 Feb
etc...
Then if you set an attribute within the month dimension called month number, when you control cube has Jun in it. The attribute will be 6. You can then spool through the cube from 6 until the value is equal to 12, bringing back the relevant month from teh new cube.
I'm sure others will tell you another way of doing it as there are many but I thought this would be the easiest to comprehend,
Jim.
Edit: You could also use the next cube and attribute for next month calculations etc...
Re: TI Process to filter subset on the basis of actual month
Posted: Thu Jul 10, 2014 4:51 pm
by dharav
Jim
Could u give me small example on process to sloop after creating control cube with 2 dimensions? It would help me to understand the concept thoroughly?
Thanks
Dharav
Re: TI Process to filter subset on the basis of actual month
Posted: Thu Jul 10, 2014 5:47 pm
by jim wood
I hope this helps...
Code: Select all
vCurrentMonth=CellGetS('Cube',dim,'Current Month); (june in this case)
vCurrentMonthNumber=AttrN(dim,vCurentMonth);
i=vCurrentMonthNumber;
Max=12;
while (i<=Max);
IF(i=vCurrwntMonthNumber);
vMonthText=vCurrentMonth;
Else;
vMonthText=vMonthText|','|CellGetS('Cube2',NumberToString(i),'Text');
EndIF;
i=i+1;
End;
This will give you
Jun,Jul,Aug,Sep,Oct,Nov,Dec
Re: TI Process to filter subset on the basis of actual month
Posted: Thu Jul 10, 2014 6:30 pm
by gstager
I would guess the reason your original code did not work is that the index order (you were looping thru DIMIX) did not reflect the natural month order. You have to be careful when relying on DIMIX to give you an expected sequence.
Re: TI Process to filter subset on the basis of actual month
Posted: Thu Jul 10, 2014 7:21 pm
by dharav
Thank you all. I would implement and let you know about the output.
Appreciate your input and valuable time.
Dharav
Re: TI Process to filter subset on the basis of actual month
Posted: Fri Jul 11, 2014 12:28 am
by rmackenzie
In a typical (for financial forecasting models) month dimension you will find Year-To-Go consolidations (YTG). Assuming a financial year runs Jan to Dec then an example would be:
- May YTG
---Jun
---Jul
---Aug
---Sep
---Oct
---Nov
---Dec
The May Year-To-Date (YTD) consolidation would include Jan through May meaning May YTD + May YTG = Full Year. YTG and YTD consolidations are very useful for a number of reporting purposes.
If you implement YTG consolidations in your month dimension then creating a 'future months' subset becomes simply a matter of adding the children of the current month's YTG consolidation. E.g.:
Code: Select all
# initialise subset for forecast months
IF ( SubsetExists ( '1t_months', 'Forecastmonth' ) = 0 );
SubsetCreate ( '1t_months', 'Forecastmonth' );
ENDIF;
SubsetDeleteAllElements ( '1t_months', 'Forecastmonth' );
# get current month and YTG consolidation
sCurrentMonth=Cellgets('1f_admin','Actual Month','Version - Month View');
sCurrentMonthYTG = sCurrentMonth | ' YTG';
# iterate current month YTG consolidation for subset members
nCounter = 1;
nMaxCount = ELCOMPN ( '1t_months', sCurrentMonthYTG );
WHILE ( nCounter <= nMaxCount );
sChildMonth = ELCOMP ( '1t_months', sCurrentMonthYTG, nCounter );
SubsetElementInsert ( '1t_months', 'Forecastmonth', sChildMonth, nCounter );
nCounter = nCounter + 1;
END;
Re: TI Process to filter subset on the basis of actual month
Posted: Fri Jul 11, 2014 3:23 pm
by dharav
Hello All /Rmackenzie
Thank You all for your inputs.
I have implemented below process:
It works fine when i need < result but when i need > result than it throws the following error(Error: Prolog procedure line (20): Unable to insert element in subset. Position of element exceeds the number of elements in subset + 1 ). I know this error is because (See the highlighted in red color). Could you let me know, how to get the value of element position 1 and to increase thereafter? Instead of SubsetElementInsert if i use subsetdeleteelement than my purpose is getting resolvedbut every year we have to create subset manually for Jan to Dec.
IF ( SubsetExists ( '1t_months', 'Forecastmonth' ) = 0 );
SubsetCreate ( '1t_months', 'Forecastmonth' );
ENDIF;
SubsetDeleteAllElements ( '1t_months', 'Forecastmonth' );
#####################################################################
sCurrentMonth=Cellgets('1f_admin','Actual Month','Version - Month View');
#####################################################################
nCounter = 1;
nCount=Attrn('1t_months',sCurrentMonth,'Count');
nMaxCount = 12;
WHILE ( nCounter <= nMaxCount );
# sChildMonth = ELCOMP ( '1t_months', sCurrentMonthYTG, nCounter );
IF (nCounter>nCount);
sChildMonth = DimensionElementPrincipalName( '1t_Months', NumberToString(nCounter) );
SubsetElementInsert ( '1t_months', 'Forecastmonth', sChildMonth, ncounter );
Endif;
nCounter = nCounter + 1;
END;
##########################################################################
Thanks
Dharav
Re: TI Process to filter subset on the basis of actual month
Posted: Fri Jul 11, 2014 9:52 pm
by declanr
The error you are getting is fairly obvious.
If the first element you find that fits your criteria is element 5, you are trying to insert it into your empty subset at position 5...
Replace it with 1 and every element will go in at position 1 forcing the other elements already in the subset further out. Or if you need it to be in specific order use subsetgetsize (...) +1
Re: TI Process to filter subset on the basis of actual month
Posted: Fri Jul 11, 2014 10:15 pm
by declanr
And on another note it would imo be cleaner to just use MDX.
Code: Select all
sDimName = '1t_months';
sSubsetName = 'ForecastMonth';
sAttributeName = 'Count';
sCurrentMonth=Cellgets('1f_admin','Actual Month','Version - Month View');
nCurrentMonth = AttrN ( sDimName, sCurrentMonth, sAttributeName );
IF ( SubsetExists ( sDimName, sSubsetName ) = 1 );
SubsetDestroy ( sDimName, sSubsetName );
ENDIF;
sMDX = '{FILTER( {TM1SUBSETALL( [' | sDimName | '] )}, [' | sDimName | '].[' | sAttributeName |'] > ' | nCurrentMonth | ')}';
SubsetCreateByMDX ( sSubsetName, sMDX, sDimName );
Assuming I haven't put in any typos the MDX should give you something along the line of:
{FILTER({TM1SUBSETALL([month])}, [month].[monthno]>5)}
If you are to use it as part of a TI datasource it may be worth converting the subset back from dynamic to static afterwards by removing and re-adding the last element etc.
Re: TI Process to filter subset on the basis of actual month
Posted: Tue Jul 15, 2014 2:53 pm
by dharav
