Building a Rolling 13Week Period Dimension

[robertk]

I am trying to build a rolling 13week Period dimension using TI. I have got the dimension populating with the 91 days (Today to 13weeks in the future).

METADATA

nCurrDate = DAYNO(TODAY);
nCurrWeek = WEEKNUM(TODAY);

WHILE(nCounter<nStop);

sysDate = DATE(nCurrDate);
currMonth = SUBST(sysDate, 4, 2);

#CHANGE THE MONTH TO A MMM FORMAT
mmmMonth = IF(currMonth@= '01', 'Jan',
IF(currMonth@= '02', 'Feb',
IF(currMonth@= '03', 'Mar',
IF(currMonth@= '04', 'Apr',
IF(currMonth@= '05', 'May',
IF(currMonth@= '06', 'Jun',
IF(currMonth@= '07', 'Jul',
IF(currMonth@= '08', 'Aug',
IF(currMonth@= '09', 'Sep',
IF(currMonth@= '10', 'Oct',
IF(currMonth@= '11', 'Nov', 'Dec')))))))))));

sCurrDate = SUBST(sysDate, 7, 2) | '-' | mmmMonth | '-' | SUBST(sysDate, 1, 2);

DimensionElementInsert(dPeriod, '', sCurrDate, 'N');

nCounter = nCounter+1;
nCurrDate = nCurrDate + 1;

END;

Now I need to consolidate the elements into weekly groups (Week1 to Week13) ie Week 1 Wed - Sun and Week 13 Mon - Tue. Without a WeekNum function I can't see any way to derive the week in which day falls. If anyone has come across this problem before or has an ideas that would be great.

Cheers,

Robert

[Neil Watson]

Robert,

One way of adding weeknum functionality is to use dimension attributes. Create a Time or Days dimension with the days of the year in, add new attributes (Text) (Right Click Dimension, Edit attributes, File Add New) of 'Day Name', 'Week Number', it's not uncommom to also have 'Next Day', 'Previous Day', 'Previous Year', and so on. It might seem like a long and labourious task but it is a one off, you can reuse the dimension and it doesn't have to be in any cube at all and can just be used for reference. Also you can create the text for the attribues in Excel and then copy them in, making numbering a little easier.

Within TI you'll need to use, ATTRN for numerics and ATTRS for strings to retrieve the attribute.

Just a couple of other things, although I suspect you haven't posted your entire metadata tab,
1. You need to set nCounter initially
2. You need to set nStop initially


HTH
Neil

[Alan Kirk]

I am trying to build a rolling 13week Period dimension using TI. I have got the dimension populating with the 91 days (Today to 13weeks in the future).

{Snip code for brevity}

Now I need to consolidate the elements into weekly groups (Week1 to Week13) ie Week 1 Wed - Sun and Week 13 Mon - Tue. Without a WeekNum function I can't see any way to derive the week in which day falls. If anyone has come across this problem before or has an ideas that would be great.

I'm not clear on a number of things that you're trying to do here.

First, you had your source code in the Metadata tab... but you haven't said what you're using as a data source. Unless there's something else that you're doing with a data source, you could (and probably should) be doing this in the Prolog tab with a data source of None because otherwise you'll be creating these elements for each and every row of data you have.

The other confusing thing is with the dates; when you said:

Week 1 Wed - Sun and Week 13 Mon - Tue.

However you posted this on a Friday, so I'll work off the assumptions that:
(a) You encountered this problem last Wednesday; and
(b) Your week ends on a Sunday, which is why you wanted the first week to end on that day.

However the latter supposition generates a problem in itself. 91 days is indeed 13 calendar weeks, but if you have some of them "hanging" like that (with fewer than 7 days in the first and last week) you won't have 13 weeks, you'll have 14. You'll only have 13 if the first day that you use happens to be a Monday.

Also, I'm not at all clear on whether this is supposed to be a "run once" arrangement, or whether you'll run it each day, or at some other intervals. If it's not a "run once", then you'd also have to either delete the consolidations or delete their child elements each time you run it.

Given all of the unknowns, I've just put together an example based on what you appear to be doing. If you need any further clarification, feel free to post further questions... but they may be answered by someone else.

(That's because it's Friday night over here, while my European colleagues arrived at work only a couple of hours ago and are trying to will themselves through one more day before the weekend. Oh, and the 45th President of the USA probably isn't even out of bed yet but probably will be in an hour or so.)

Prolog, data source None:

Code: Select all

dperiod = 'zTestRollingDates';

nCounter = 0;
nStop = 91;

#This would be used in real life.
nCurrDate = DAYNO(TODAY);

# But in this case I've hard coded the date 
# to agree with the example in the post.
nCurrDate = DAYNO('08-12-03');

l_Week = 1;
s_Week = 'Week ' | Trim ( Str ( l_Week, 2, 0 ) ); 

DimensionElementInsert(dPeriod, '', s_Week, 'C');

WHILE(nCounter<nStop);

    sysDate = DATE(nCurrDate);
    currMonth = SUBST(sysDate, 4, 2);

#CHANGE THE MONTH TO A MMM FORMAT
    mmmMonth = IF(currMonth@= '01', 'Jan',
     IF(currMonth@= '02', 'Feb',
     IF(currMonth@= '03', 'Mar',
     IF(currMonth@= '04', 'Apr',
     IF(currMonth@= '05', 'May',
     IF(currMonth@= '06', 'Jun',
     IF(currMonth@= '07', 'Jul',
     IF(currMonth@= '08', 'Aug',
     IF(currMonth@= '09', 'Sep',
     IF(currMonth@= '10', 'Oct',
     IF(currMonth@= '11', 'Nov', 'Dec')))))))))));

    sCurrDate = SUBST(sysDate, 7, 2) | '-' | mmmMonth | '-' | SUBST(sysDate, 1, 2);

# This will return 0 if the day of week is a Sunday. (1 for Monday through to 6 for Saturday.)
    DayIndex = Mod( nCurrDate + 21915, 7); 

    DimensionElementInsert( dPeriod, '', sCurrDate, 'N');

    DimensionElementComponentAdd( dPeriod, s_Week, sCurrDate, 1);

    nCounter = nCounter+1;
    nCurrDate = nCurrDate + 1;

# Increment the Week consolidation if the day is a Sunday
    If (DayIndex = 0 );
        l_Week = l_Week + 1;
        s_Week = 'Week ' | Trim ( Str ( l_Week, 2, 0 ) ); 
        DimensionElementInsert(dPeriod, '', s_Week, 'C');
    EndIf;

END;

[robertk]

Thanks Neil and Alan for you suggestions and help.

You are right, the code is in the prolog tab., and I should have clarified that the dimension is recreated each day and comprise 12 complete 7 day weeks, with there being a hanging 1st and 13th week when Day 1 is not a Monday. By changing nStop to equal 84, Alan's code works perfectly (the MOD(DAYNO(TODAY)/7) is exactly what was needed).

Cheers,

Robert