Planning Analytics

 View Only

  • 1.  Week Level in Time Dimension

    Posted 19 days ago

    Hello,

    I am a newbie here to writing TI processes. The requirement is to store a snapshot of employee headcount weekly. The data source is a text file and there is no date in the file.

    For time, my thought is to have a date dimension with year>quarter>month>week ( 1 through 52). Then I would use a variable to define the week of year based on the current date when the file is loaded. I can't figure out how to extract the week number from a date variable. 

    Has anyone done something similar? Any suggestions on how to determine the week based on current date? Maybe there's a better approach?

    Thank you for your guidance!



    ------------------------------
    Jeremy Aude
    ------------------------------


  • 2.  RE: Week Level in Time Dimension
    Best Answer

    Posted 19 days ago

    You should look at the FormatDate TI command: https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=dttf-formatdate

    The details of the formats supported are at https://unicode-org.github.io/icu/userguide/format_parse/datetime/

    I suggest that w - week of year - is what you want, eg (not tested myself)

    vNow = now;
    asciioutput('d:\temp\test.csv',
    numbertostring(vnow),
    formatdate(vNow,'yMMMdEw')
    );

    The date string above has lots of other formats you probably don't want but could be instructive.



    ------------------------------
    David Usherwood
    ------------------------------

    Original Message


  • 3.  RE: Week Level in Time Dimension

    Posted 18 days ago

    Thanks David this is exactly what I was trying to accomplish! I did fully understand the unicode formatting and I was also trying to use the Today() function instead of Now.

    I appreciate your help!



    ------------------------------
    Jeremy Aude
    ------------------------------

    Original Message


  • 4.  RE: Week Level in Time Dimension

    Posted 18 days ago

    Hi,

    not sure how Unicode defines the first week in a year, but be aware that there are different approachees to determine that, depending on which country your in. :)

    Excel has for instance two functions to determine the calender week, weeknum and isoweeknum. Thus another way to create your hierarchy could be to create a neat csv via excel, having formulas to calculate every needed field/attribute/element and create a TI to load it. This way you're able to easily extend your dimension through extending the data area in that excel via drag and drop.

    This is what I've done lately:

    The accompanying .xls has the formulas in it to calculate all values.

    Kind regards

    Matthias



    ------------------------------
    Matthias Holthus
    ------------------------------

    Original Message


  • 5.  RE: Week Level in Time Dimension

    IBM Champion
    Posted 15 days ago
    Edited by Wim Gielis 15 days ago

    Here is a short overview:

    # Calculate the weeknumber for a given date
    # (Weeknumber does not contain the year but could be added too)
    pDate = '2024-06-21';


    # OPTION 1
    nDate = DayNo( pDate );
    v = Timst( nDate, '\Y-' ) | NumberToStringEx( nDate, 'ww', '', '' );
    TextOutput( 'weeknumber.txt', v );


    # OPTION 2
    vDateFormat = 'yyyy-MM-dd';
    nDate = ParseDate( pDate, vDateFormat );
    v = FormatDate( nDate, 'w' );
    TextOutput( 'weeknumber.txt', v );


    # OPTION 3 - ISO weeknumber
    # Refer to: https://www.tm1forum.com/viewtopic.php?t=16386



    ------------------------------
    Wim Gielis
    ------------------------------

    Original Message