Page 1 of 1
Dynamic Subset for Week
Posted: Fri Oct 05, 2012 4:12 am
by beek
Dear all TM1 gurus,
I have a question here, hope you can share with me your views. I have a Week dimension. I would like to create a dynamic subset to reflect only the current week + the subsequent 30weeks.
I have a Setting cube to maintain the current Week.
I know using TI is possible, but I'm looking at if there's any other options which might be lower maintenance..
Thank you
Beek
Re: Dynamic Subset for Week
Posted: Fri Oct 05, 2012 4:47 am
by PlanningDev
Use MDX functions and create a dynamic subset in the subset editor. Take a look at the set expressions like head() and periodstodate().
Re: Dynamic Subset for Week
Posted: Fri Oct 05, 2012 6:04 am
by rmackenzie
beek wrote:I have a Week dimension. I would like to create a dynamic subset to reflect only the current week + the subsequent 30weeks.
...I know using TI is possible, but I'm looking at if there's any other options which might be lower maintenance..
In this case I would prefer to have a process running once a week to do this task simply because once you have defined current week plus the next thirty then the definition doesn't need to change for 7 days.
It is a wasteful performance overhead having this definition stored dynamically. The code to build the subset should also be generic:
Code: Select all
# initialise subset
sDimName = 'Week';
sSubName = 'Current weeks plus thirty';
IF ( SubsetExists ( sDimName, sSubName ) = 1 );
SubsetDeleteAllElements ( sDimName, sSubName );
ELSE;
SubsetCreate ( sDimName, sSubName );
ENDIF;
# insert start week that you got from your Settings cube
sThisWeek = CellGetS ( 'Settings', 'Current Week', 'Text Setting' );
SubsetElementInsert ( sDimName, sSubName, sThisWeek, 1 );
# insert next 30 weeks
# relies on week dimension have a next week attribute :-)
nCounter = 1;
nMaxCount = 30;
WHILE ( nCounter <= nMaxCount );
sNextWeek = ATTRS ( 'Week', sThisWeek, 'Next week' );
SubsetElementInsert ( sDimName, sSubName, sNextWeek, nCounter + 1 );
sThisWeek = sNextWeek;
nCounter = nCounter + 1;
END;
DISCLAIMER - this code is entirely untested. Caveat Emptor. YMMV. Cheers.
Re: Dynamic Subset for Week
Posted: Fri Oct 05, 2012 9:23 am
by beek
Thank you (both) for the replies.. Yes, for performance wise, I think I will just stick to TI process.
Re: Dynamic Subset for Week
Posted: Fri Oct 05, 2012 11:21 am
by Wim Gielis
PlanningDev wrote:Use MDX functions and create a dynamic subset in the subset editor. Take a look at the set expressions like head() and periodstodate().
Did you mean LastPeriods by that?