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!

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.

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!

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

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
------------------------------