Zero out all dates in current month

[grantm15]

I want to zero out the current and previous month's data from a cube on a nightly basis before I load it again. The leaf level date is in string format, i.e., '2013-01-01'. I have the code for zeroing out data properly but am having difficulty figuring out how to zero out the current and previous month's data based on today's date dynamically. So if today's date is 2-25-13, how do I zero out data of everyday in Feb (current month) and January (previous month)? Thanks for your time and consideration.

[declanr]

I want to zero out the current and previous month's data from a cube on a nightly basis before I load it again. The leaf level date is in string format, i.e., '2013-01-01'. I have the code for zeroing out data properly but am having difficulty figuring out how to zero out the current and previous month's data based on today's date dynamically. So if today's date is 2-25-13, how do I zero out data of everyday in Feb (current month) and January (previous month)? Thanks for your time and consideration.

This could possibly benefit from a post of its own.

Essentially you would just need to create the subset containing those date elements and then assign that to your view to be zero'd out (I am assuming that as per your comment you use the ViewZeroOut function.)

I haven't tested this below code but just written it now (and it is using the date format yy-mm-dd) so you will need to tweak it a little and possibly remove some typos etc but essentially it would create a subset for all dates from NOW going backwards until the Month is no longer this one or the prior one.

Code: Select all

sDateNow = Today( 0 );
nDateNow = DayNo ( sDateNow );
sMonth = Subst ( sDateNow, 4, 2 );
nMonth = StringToNumber ( sMonth );

If ( nMonth = 2 );
     nStop = 12;
ElseIf ( nMonth = 1 );
     nStop = 11;
Else;
     nStop = nMonth - 2;
EndIf;

iCount = 0;
iMax = 100;
While ( iCount < iMax );
     nDate = nDateNow - iCount;
     sDate = Date ( nDate );
     SubsetElementInsert ( sDim, sSubset, sDate, SubsetGetSize ( sDim, sSubset ) + 1 );
     nCheck = StringToNumber ( Subst ( Date ( nDate - 1 ), 4, 2) );  
     If ( nCheck = nStop );
         iCount = 100;
     Else;
     iCount = iCount + 1;
     EndIf;
End;

[grantm15]

Thanks declanr, I will give this a shot. I am using the viewzeroout function. Essentially this was my simple code for zeroing out a day with the day hard coded in:


sCube='Margin_Cube';
sObjName='sys_' | getprocessname();
if(viewexists(sCube, sObjName)=1); viewdestroy(sCube, sObjName); endif;
ViewCreate(sCube, sObjName);

sDim='Day_Week_Year_DIM';
sElem='2012-11-02 00:00:00.000';
if(subsetexists(sDim, sObjName)<>1); subsetcreate(sDim, sObjName); endif;
subsetdeleteallelements(sDim, sObjName);
subsetelementinsert(sDim, sObjName, sElem, 1);
ViewSubsetAssign(sCube, sObjName, sDim, sObjName);

ViewZeroOut(sCube, sObjName);
ViewDestroy(sCube, sObjName);


SubsetDestroy(sDim, sObjName);

[Alan Kirk]

This could possibly benefit from a post of its own.

Agreed; it has nothing to do with my Using Dates And Times crib.

The posts have been split from that, re-titled and moved to the correct forum.

[paulsimon]

I want to zero out the current and previous month's data from a cube on a nightly basis before I load it again. The leaf level date is in string format, i.e., '2013-01-01'. I have the code for zeroing out data properly but am having difficulty figuring out how to zero out the current and previous month's data based on today's date dynamically. So if today's date is 2-25-13, how do I zero out data of everyday in Feb (current month) and January (previous month)? Thanks for your time and consideration.

I think that this bit of code will do what you want, but please see the however below.

Code: Select all

vThisPro = 'EX_Zero Out My Cube Example' ;

vDim = 'EX_Date' ; 

vSub= 'zZeroOut_' | vThisPro ;

vToday = now() ;

vCurrMth = TIMVL(vToday,'M') ;

vCurrYear =  TIMVL(vToday,'Y') ;

vPrevMth = vCurrMth - 1 ;

IF( vPrevMth < 1 ) ;
  vPrevMth = 12 ;
  vPrevYear = vCurrYear - 1 ;
ELSE ;
  vPrevYear = vCurrYear ;
ENDIF ;

# Make up Start Date in the form YYYY-MM-DD

vPrevYearS = NumberToString( vPrevYear) ;
vPrevMthS = NumberToString( vPrevMth) ;

vStartDate = DAYNO( vPrevYearS | '-' | vPrevMthS | '-01' ) ; 

IF( SubsetExists( vDim, vSub ) = 0 ) ;
  SubsetCreate(  vDim, vSub ) ;
ELSE ; 
  SubsetDeleteAllElements( vDim , vSub ) ;
ENDIF ;

vDate = vStartDate ;
vCount = 0 ;
WHILE( vDate <= vToday ) ;
  vCount = vCount + 1 ;
  vDateElem = TIMST( vDate, '\Y-\m-\d' ) ;
  SubsetElementInsert( vDim, vSub, vDateElem, vCount ) ;
  vDate = vDate + 1 ;
END ;

# Continue to make up the View to zero out the cube
# assign this subset to the view using ViewSubsetAssign
# ....

Although the above works, I would not do it this way.

The Day dimension that I actually use has Days consolidating to Months.

I have an Info Cube which holds the Current Month in the system and the Previous Month. It also holds the current date in element format. These are driven from elements that hold the current Day, Month and Year. If you needed this to update automatically you could do this via a process run from a chore every night.

To create the subset you would then just need to loop through all components of the Prev and Current Month using a WHILE loop and ELCOMP

Alternatively you could use a loop that starts with the first component of the Previous Month and uses DNEXT in a WHILE loop to advance the element until the element matches the element from the Info Cube given by Curr Date. This assumes that your dimension has all the dates before any consolidations for months. However, at least with Time Dims, you can control the order of the elements. This also assumes that you have a Day dimension with all dates across all years, rather than separate Day and Year dimensions, so that it handles the situation where the previous month is in the previous year correctly.

Alternatively you could just create a Dynamic Subset using MDX.

As ever there are lots of ways to do this, and as ever, having the right design for the dimension can make it a lot easier.

Regards

Paul Simon

[grantm15]

I also have days consolidated into Months. So, really what I am trying to do I guess is to find today's date and translate it into format yyyymm (for Current Month & Previous Month). So, in essence, all I want is to translate today's date into the current and previous month in yyyymm format. My dimension rolls string dates, i.e., '2012-11-02 00:00:00.000' into months (format yyyymm), so I really just needed to zeroout my cube with the month elements of 201303 and 201302 if we we doing this using today's date.

Thanks again.