Using Dates And Times In TM1

Ideas and tips for enhancing your TM1 application
Locked
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Using Dates And Times In TM1

Post by Alan Kirk »

We've had a few questions on use of dates and times in TM1 over the life of the forum, most recently last week. I've recently been refurbishing my TI Standard Code manual at work and have upgraded the section dealing with that topic. It's provided below as a reference for new users (or as a crib for more established ones).

About TM1 Dates Vs Excel Dates
Both TM1 and Excel use a "date serial" system in which any date that you use in calculations is represented as a positive integer value. That integer value is calculated from an arbitrary starting date. Adding whole numbers to a specific serial date moves you forward through the calendar a day at a time, and subtracting whole numbers moves you backwards... as long as you don't go past the starting date of the serial number system and end up with a negative value. Times are represented as fractions of a day; 0.25 for 6am, 0.5 for noon, 0.75 for 6pm and so on.

Excel Dates
In the case of Excel for Windows, the starting date is 1 January 1900. That is, if you enter the value 1 into a cell in Excel and format it as a date, you'll see the value as 1 January 1900. 2 will be the 2nd of January 1900, 3 the 3rd of January, and so on. 367 represents 1 January 1901 because Excel treats 1900 as having been a leap year with 366 days (see below). In other words, every full day that passes adds 1 to the serial date. Jumping forward many decades, Friday 1 January 1960 is day number 21,916. The significance of that date to TM1 users will be seen later.

It's important to remember that the above relates to Excel only, and not to Access, SQL Server or other database products (or Visual Basic, for that matter). In Access, for example, the range of valid dates is 1 January 100 to 31 December 9999, the same range that can be stored in a VB or VBA variable with a Date data type.

Excel And The Macintosh
Macintosh systems use a start date of 1 January 1904, neatly bypassing the 1900 leap year issue described below. However that does mean that there's a 4 year discrepancy between the serial date values in a workbook created in Excel for Windows, and one created in Excel for the Mac. Fortunately under Tools -> Options-> Calculation (on pre-2007 versions of Excel) you'll find a workbook option called 1904 Date System. If that's checked, Excel knows that the workbook came from a Macintosh and will adjust its date calculations accordingly.

Excel Times
As noted in the introduction, times are calculated as a fraction of a day. For example 1.5 represents noon on 2 January 1900. 1.75 represents 6pm on 2 January 1900.

The Y1.9K Bug
Be aware that there is a bug in Excel's calculation of the year 1900. 1900 was not a leap year, but Excel treats it as if it were. This is a carry-over from a bug that was in Lotus 1-2-3, and was intentionally included in Excel to maintain compatibility with that program. As Microsoft explained it in KBA 214326 (http://support.microsoft.com/kb/214326):
When Lotus 1-2-3 was first released, the program assumed that the year 1900 was a leap year, even though it actually was not a leap year. This made it easier for the program to handle leap years and caused no harm to almost all date calculations in Lotus 1-2-3.

When Microsoft Multiplan and Microsoft Excel were released, they also assumed that 1900 was a leap year. This assumption allowed Microsoft Multiplan and Microsoft Excel to use the same serial date system used by Lotus 1-2-3 and provide greater compatibility with Lotus 1-2-3. Treating 1900 as a leap year also made it easier for users to move worksheets from one program to the other.

Although it is technically possible to correct this behavior so that current versions of Microsoft Excel do not assume that 1900 is a leap year, the disadvantages of doing so outweigh the advantages.
From 1 March 1900 onward Excel's dates are correct, but if you format the number 1 using the format dddd, mmmm dd, yyyy you'll get the result Sunday, 1 January 1900. That is incorrect; 1 January 1900 was a Monday, not a Sunday. This day of week error continues until you reach 1 March, which is the first truly correct date in the Excel calendar.

Day Zero And Before In Excel
If you use the value zero and display it in date format you'll get the nonsense date Saturday 0 January 1900. If you try to format a negative value as a date, you'll just get a cell full of hash marks. Similarly if you try to obtain a date serial number using Excel functions like DateValue, you can only do so for dates on or after 1 January 1900. An attempt to specify an earlier date will result in an error.

The 1904 (Macintosh) system starts from zero. (1 January 1904 has a value of 0, not 1. Excel's on-line help describes the Mac system as starting from January 2, but that's probably easier than explaining to users why a serial date value of 0 works on the Mac but not Excel.) Negative numbers won't generate an error, but the number will be treated as absolute. That is, both 1 and -1 will be treated as 2 January 1904.

TM1 Dates
In the case of TM1, the arbitrary starting date is 1 January 1960. Why the difference?

CAUTION: There was a change in Planning Analytics 2.0 which allows you to set a server config parameter to use Excel serial dates instead of legacy TM1 ones. See this thread for details.

This is speculation, but early versions of TM1 (prior to version 8.4) only supported 2 digit years in the TM1 date functions. That means that you had only 99 years to play with. Had the starting date been 1 January 1900, then in the mid-90s when TM1 was getting a foothold in the market you would have had only about 4 or 5 years worth of future dates that could be calculated by the TM1 date functions. By 1 January 2000, you'd be completely out of luck. Starting at 1 January 1960 gave 40 years in the 20th century and 59 years in the 21st, which was a much more usable range of dates.

From version 8.4 many TM1 functions support 4 digit year dates in the range 1 Jan 1960 to 31 Dec 9999. Dates prior to 1 Jan 1960 are still not supported which can be a pain for certain H.R. related models, but doing so now would probably break a great many applications.

A major difference between TM1 and (Windows) Excel is that TM1 starts from 0 instead of 1. That is, 1 January 1960's serial date value is 0.

If you have a TM1 serial date and want to get the corresponding Excel serial date, you need to add 21,916.

Conversely if you have an Excel serial date and want to get the corresponding TM1 serial date, you need to subtract 21,916.

However, TM1 can no more handle negative dates than Excel can. If you try to use a negative serial date number in a TM1 time calculation, you will also get an error.

In the real world you won't normally be importing an Excel-sourced date as a serial number, but rather as a formatted string which you then need to parse to feed it into one of the TI date functions described later. (The exception may be if you're doing a DBSend into a cell, with the cell value being used in date calculations within rules. In such a case you may need to modify the value before sending it to ensure that it records TM1's serial date, and not Excel's.)

TM1 Times
TM1 times work in the same way as Excel's; just as integers represent whole days, decimal remainders represent times. 0.25 will represent 6am, 0.5 represents noon, 0.75 represents 6pm and so on.

Date And Time Functions In Excel Vs Date And Time Functions In TM1
Both Excel and TM1 provide functions for obtaining serial dates and times. TM1's set is more limited (and generally more focussed on date to string conversions and vice versa while Excel's tend to be more "value- (numeric) oriented), but many of them have equivalents in some form or another.

When you're importing dates into a TI process, the data source won't be Excel itself. Even if the data was originally in an Excel worksheet which was then exported to a .csv /.cma file, dates typically won't be in Excel's serial date format but rather written as a formatted date string.

Nor can TurboIntegrator use dates which are in the internal database formats of systems like Access or SQL Server. It can only interpret values coming from any data source as either numeric values, or string values. Dates should therefore normally be brought across as strings, which allows you to parse them to obtain their individual components (year, month, day, hour, minute and second, as applicable) using the SubSt() rules function. For example, if the data source has the value 21/03/10, you could parse it and convert it to a TM1 serial date as follows:

Code: Select all

s_Day = Subst ( DateOfEntry, 1, 2);
s_Month = Subst ( DateOfEntry, 4, 2);
s_Year = Subst ( DateOfEntry, 7, 2);
s_TM1DateString =  s_Year | '-' | s_Month | '-' | s_Day;
l_DateSerial = DayNo( s_TM1DateString );
(Obviously this could be done in a single step, omitting the assignment of individual component values to variables. However the above is broken down to help illustrate the process.)

To make life easier it's suggested that you ensure that the data source outputs dates in a strict date format; specifically, with 2 digits for the day and month, using leading zeros if necessary. That ensures that you can always refer to the same character positions in your SubSt() formulas.

To Get The Serial Date Of The Current Day
Excel: = Today()
TM1: There is no exact equivalent. Instead, you can either:
  • Use the Now() rules function which returns the date and time, and extract the integer component of it; or
  • Use the Today() rules function (which, unlike Excel's, returns a string, not a value). That will give you a string in YY-MM-DD or YYYY-MM-DD format which you can then feed to the DayNo() function.
For example:

Code: Select all

l_TodaySerial = DayNo( Today(1) );

The (1) argument isn't strictly necessary for years before 2060 (it forces the returned string to have a 4 digit year) so this could also be expressed as

Code: Select all

l_TodaySerial = DayNo( Today );
Be aware that there is an error in the documentation (as late as version 9.4) which states that DayNo will only return serial values from 01 Jan 1960 to 31 Dec 2059. That's incorrect; it will return values in 2060 and beyond.

To Get A Serial Date For Any Date Which Is In String Format
Excel's functions are in large part regulated by the regional settings of the user. In Australia, for example, functions would use the DD-MM-YY format for a date argument, while in the U.S.A. they would use MM-DD-YY. To ensure consistency and eliminate regional variations as an issue, TM1 works with only one order of the date components; YY-MM-DD or, in versions 8.4 onwards, YYYY-MM-DD.
Excel: =DATEVALUE("31-12-2002")(Australian / European format)
TM1: The string has to be in the TM1 specified format (i.e., YY-MM-DD or YYYY-MM-DD), or parsed into that. You can then use the DayNo() rules function like so:

Code: Select all

l_DateSerial1 = DayNo('02-12-31');

or if you prefer:

Code: Select all

l_DateSerial2 = DayNo('2002-12-31');
To Get A Serial Date For Any Date Where You Have The Year, Month And Day
Excel: =DATE(YYYY,M,D) where YYYY is the year in numeric format (must be 4 digits), M is the month (the leading 0 is optional and will be dropped), and D is the day of the month (same). If the arguments are in text format, convert them with the Val command first. (Eg, =DATE(VALUE("2010"),3,21).)
TM1: If you have the day, month and year values in string format, concatenate them with the pipe operator to create a standard TM1 date string. For example:

Code: Select all

s_Datestr = '2010' | '-' | '03' | '-' | '21';
though the values would normally be variables rather than literal strings. After that you can use the DayNo() function to convert the string into a serial date.
If you have the arguments in numeric format, use the DateS function to convert them to a string first, then feed that string into DayNo like so:

Code: Select all

l_SerialDate = DayNo(DateS(2010, 3, 21));
Again the argument values are more likely to be variables rather than constant numbers; the above is for illustration only.

To Get A String Showing The Date For A Given Serial Number
Excel: Either =TEXT(A1,"YY-MM-DD") or =TEXT(A1,"YYYY-MM-DD") where A1 contains the serial number that you want to get, although more typically in Excel you'll simply use the value itself and format it as a date.
TM1: Use the Date() rules function. For example:

Code: Select all

s_Date1= Date(14544);
will return "99-10-27", and

Code: Select all

s_Date2= Date(14544, 1);
will return "1999-10-27".

Remember that if you want this for the current day, using the Today() or Today(1) (to get a 4 digit year) function is the most direct approach.

To Get The Day, Month Or Year Component Of A Date As A Value
Excel: You have to feed a date serial number to the Day(), Month() or Year() functions. If the date is in string format, convert it using the DateValue() function (described earlier) first.
TM1: If you have the value as a serial date value, use the TimVl() rules function which allows you to extract any of the time or date components (year, month, day, hour, minute or second) from it. Alternatively you can convert the serial date's value to a string using the Date() rules function (described above) and use the Day(), Month() and Year() rules functions, but using TimVl() is more direct.
If the date is a string (using the TM1-specified formats of YY-MM-DD or YYYY-MM-DD), you can use the Day(), Month() and Year() rules functions.

To Get The Weekday Of A Date As A Value
In some cases you need to know what the day of the week is. For example, you may want a TI process to only do certain tasks from Thursday to Saturday. To that end you need to determine the current day, ideally by getting a numeric code for the day, and compare it to the range of days that you want the task to run on.
Excel: Use the =WEEKDAY() function. An optional second argument allows you to set the return value as either (1 or omitted) Sunday = 1 to Saturday=7, (2) Monday = 1 to Sunday = 7 or (3) Monday = 0 to Sunday = 6.
TM1: There isn't a built-in option for this. However we use a standard block of code which will do that:

Code: Select all

# 0 = Sunday, 1 = Monday to 6 = Saturday.
l_DayOfWeek= Mod ( DayNo( Today ) + 21915, 7);
Remember that the Today() function returns the current date as a string, so you could substitute any string format date (from 1 Jan 1960, and in YY-MM-DD or YYYY-MM-DD format, of course) to get the weekday of an alternative date.

To cover the most commonly asked questions about the equation, "What's 21,915"? This equation could also be used with Excel (with a small amount of modification) and 21,915 is the difference between Excel's serial date and TM1's. "Hang on, didn't you say that the difference was 21,916?" Yes. I also said that there was a bug in the treatment of the (non-) leap year in 1900 which throws the day of week values out by 1 day for the first two months of that year. That's why the difference.

To Return The Date Or Time As Timestamps
Sometimes you'll want to return a string representing the current date or time. In TM1 these values may be used as part of a log file name when doing an AsciiOutput, or as part of the content of such a log.
Excel: Use the =Text() function, which gives you a full range of formatting string options.
TM1: The most flexible way is to use the TimSt() rules function. That needs to be fed a serial date and/or serial time value, but if it's the current time you're after you can easily get that from the Now() rules function.

For example, to obtain a timestamp in the format YYYYMMDDHHIISS (where II stands for minutes to avoid confusion with the more commonly used M for Months) you could use:

Code: Select all

s_TimeStamp1 = TimSt (Now, '\Y\m\d\h\i\s');
Be aware that unlike many TM1 functions the format string that you specify is in some cases case sensitive (for example lower case m will return the month in 01 to 12 format while upper case returns it in JAN to DEC format) and space sensitive (if you put spaces between the arguments then there will be spaces in your output).

For example the formula shown above will return (on the day of writing) 20100321073654

However the following:

Code: Select all

s_TimeStamp2 = TimSt (Now, '\Y \M \d \h \i \s');
(note the capitalised M and the spaces between the flags in the second argument) yields the output 2010 MAR 21 07 36 54.

The Today() rules function will return the current date and the Time() rules function will return the current time, but you have no control over the formats that are returned.

A Quick Crib Of TM1 Date Functions

Functions Returning A Serial Date Or Numeric Value
  • Day. Input: A date string in TM1 format (YY-MM-DD or (8.4+) YYYY-MM-DD). Output: The day of month component of the date (1 to 31), in numeric format.
  • DayNo. Input: A date string in TM1 format (YY-MM-DD or (8.4+) YYYY-MM-DD). Output: The TM1 serial date counting from 0 = 1 January 1960.
  • Month. Input: A date string in TM1 format (YY-MM-DD or (8.4+) YYYY-MM-DD). Output: The month component of the date (1 to 12), in numeric format.
  • Now. Input: None. Output: Serial date and time based on the current server time. Be aware that although this function returns a value with a very large number of decimal places, it is still only accurate to about a second as Duncan P pointed out. The function will return fractions of a second in its value but is only updated around once per second, making more granular values worthless.
  • TimVl. Input: A TM1 serial date / time value. Output: A numeric value giving the component of the date that you specify; eg the hour, or the month. If you want a year beyond 2059 you need to specify an optional Extended Years argument.
  • Year. Input: A date string in TM1 format (YY-MM-DD or (8.4+) YYYY-MM-DD). Output: The year component of the date (1960 onwards), in numeric format.
Functions Returning A String
  • Date. Input: A TM1 serial date value. Output: YY-MM-DD or YYYY-MM-DD (8.4 or higher).
  • DateS. Input: Year, month and day in numeric format. Output: YY-MM-DD or YYYY-MM-DD (8.4 or higher).
  • Time. Input: None. Output: The current server time in HH:MM format. (Does not return seconds, hours are in 24 hour format.)
  • TimSt. Input: A TM1 serial date / time value. Output: A time and/or date string, the content of which depends on the flags that you set in the arguments.
  • Today. Input: None. Output: The current server date in YY-MM-DD or YYYY-MM-DD format.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
tonester30
Posts: 2
Joined: Mon May 03, 2010 7:21 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2007

Re: Using Dates And Times In TM1

Post by tonester30 »

Alan,

I was wondering if you have ever encountered an issue what we are currently having? We have changed from allowing the user to input within the Web to now allowing them to input into an Excel spreadsheet. In doing so our date are now off. For instance in the Web when the user enters "01/01/2010" (US format) the format held in TM1 as originally input, but now when the user enteres "01/01/2010" in Excel it saves as "40179" in TM1.

Please help if you can.

Thank you,

Tonester
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Using Dates And Times In TM1

Post by Alan Kirk »

tonester30 wrote: I was wondering if you have ever encountered an issue what we are currently having? We have changed from allowing the user to input within the Web to now allowing them to input into an Excel spreadsheet. In doing so our date are now off. For instance in the Web when the user enters "01/01/2010" (US format) the format held in TM1 as originally input, but now when the user enteres "01/01/2010" in Excel it saves as "40179" in TM1.
I assume that this is a String cell.

I don't have an instance of Web running at the moment and you haven't indicated whether you're talking about Websheets or the Web cube viewer; most likely the latter but either way, the problem is Excel, not TM1.

Excel has a few incredibly irritating habits, one of which is that if an input looks like a date, Excel will "help" you by converting it to a date before storing it in the cell. You may think that you want to store text, but Excel knows better and will instantly convert it to the type of serial number that I described in the original post (in this case 40179 for 1 Jan 2010), and will apply formatting to make it appear as a date. (It does the same thing with data in that format that comes in as a .csv, bypassing the text import dialog.)

And any time, for there have been many times, that someone raises this as "{Bleep}ing irritating behaviour that we should be able to override in our options", MS sticks its collective fingers in its ears and says "la-la-la, we can't hear anything but praise for our new ribbon, er, if anyone ever gives us any".

The usual workaround is to pre-format the cells as text to prevent the conversion. Unfortunately, you can't do that with DBRW formulas in an Excel sheet since it will stop them from both displaying and working properly.

The only two options that I know of are either:
(a) Have the users input to blank cells which are pre-formatted as text, and have DBS formulas located elsewhere on the sheet send the values to the cube; or
(b) House-train the users to prefix their entry with a single quote mark. If Excel receives an input of '01/01/10 instead of just 01/01/10, it'll know that you mean text and won't do the pre-storage conversion. Consequently the text will go into the cube cell "as is".
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
tonester30
Posts: 2
Joined: Mon May 03, 2010 7:21 pm
OLAP Product: TM1
Version: 9.4
Excel Version: 2007

Re: Using Dates And Times In TM1

Post by tonester30 »

Alan,

Thank you very much for the information and for the quick reply. It is greatly appreciated.

Tonester.
KWS
Posts: 3
Joined: Tue Oct 19, 2010 9:03 pm
OLAP Product: TM1
Version: 9.5.x
Excel Version: 2003

Re: Using Dates And Times In TM1

Post by KWS »

I realize this is an old post, but I came across it looking for a similar problem.

I've known about the single quote trick for writing dates to Excel for a while now and that has worked for me in Perspectives up until today. I have one workbook writing to a string element that, depending on how I set up the excel cell, would either convert the string to a date index number, or just fail to update the TM1 cube all together. The DBRW would send the update, then return the original value of the cell. Using the single quote wouldn't hold my string value.

Solution was to format the element in TM1. Using the 'Edit Element Attributes' window, I found my string elements, chose Format..., then put in a custom format of 'mm/dd/yyyy'.

I've never needed to do that in the past and currently have other string elements in other dimensions that don't require this formatting. But if you run into this problem, try this solution.
nicola531
Posts: 43
Joined: Thu Oct 22, 2009 7:58 am
OLAP Product: TM1
Version: 9.5.1
Excel Version: 2007

Re: Using Dates And Times In TM1

Post by nicola531 »

I suppose I've got a date-like problem.
I read the posts above and I realized that "my" problems with dates and DBRW are common problems.
The fact is that I've got cell that appears like feb-10 but if I look at the cell content i can see something like 40969 (serial)
Since this is a coordinate to insert some data into a cube and my dimension is feb-10 I said to me to put 40969 as alias.
When i try again the DBRW it doesn't work.
So I tried to use the TEXT function in excel to convert the 40969 in text and it works.
My point now is: does DBRW accept as coordinate an integer or should only be a text?

I would like to prevent the user to cast their date with the TEXT function milions of times.

Any suggestion is appreciated.

Thanks,

Nicola
Wim Gielis
MVP
Posts: 3098
Joined: Mon Dec 29, 2008 6:26 pm
OLAP Product: TM1, Jedox
Version: PAL 2.0.9.18
Excel Version: Microsoft 365
Location: Brussels, Belgium
Contact:

Re: Using Dates And Times In TM1

Post by Wim Gielis »

Hi there

If A2 is a date, one simple trick would be to use, instead of

=DBRW(..., A2,...)

=DBRW(..., ""&A2,...)

This will cast it as a string.
Best regards,

Wim Gielis

IBM Champion 2024
Excel Most Valuable Professional, 2011-2014
https://www.wimgielis.com ==> 121 TM1 articles and a lot of custom code
Newest blog article: Deleting elements quickly
beek
Posts: 63
Joined: Wed Sep 14, 2011 3:10 am
OLAP Product: TM1
Version: PA 2.0
Excel Version: Office 365

Re: Using Dates And Times In TM1

Post by beek »

Hi there,

I have a question pertaining to Week. Does TM1 has the Week function ? Example, today (30th May 2013) should be week 22.
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Using Dates And Times In TM1

Post by Alan Kirk »

beek wrote:Hi there,

I have a question pertaining to Week. Does TM1 has the Week function ? Example, today (30th May 2013) should be week 22.
Says who?

It's not going to be the same week number for a business which uses a financial year as it is for one which uses a calendar year. For us, for example, it's week 49. Also, even if you're counting calendar year weeks it depends on whether you regard week 1 as the week that the 1st of January falls into, or the first full week of January. Not to mention whether you consider Monday or Sunday (or, indeed, some other day) to be the first day of the week.

Consequently a week number function would be meaningless.

The usual way of approaching this is to have a lookup cube in which you can store dates as one dimension, and the financial week or month that it falls into (amongst other information, as needed) as another dimension. In that way you can use a DBRW or CellGetN function to retrieve the week of the date as you see it in your calendar.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
beek
Posts: 63
Joined: Wed Sep 14, 2011 3:10 am
OLAP Product: TM1
Version: PA 2.0
Excel Version: Office 365

Re: Using Dates And Times In TM1

Post by beek »

hmm .. I understand where you coming from. Different company will have different financial year, but I thought Week concept should have some standard to follow. I do come across some diary/calendar indicating the week number, hence my understanding is that Week concept could be something generic.

Currently we are storing the week information in dimension (eg Start Date/End Date as Attribute). I'm thinking if there's any TM1 function which I can made used of, so I no need to rely on the static dimension..

If there's no other better way, maybe I will just stick to the dimension .. :)
User avatar
jim wood
Site Admin
Posts: 3951
Joined: Wed May 14, 2008 1:51 pm
OLAP Product: TM1
Version: PA 2.0.7
Excel Version: Office 365
Location: 37 East 18th Street New York
Contact:

Re: Using Dates And Times In TM1

Post by jim wood »

Beek,

I think you'll find that Alan is right. Every business, in every industry, in every country is different. The same can be said if you are looking Periods. I watched a video yesterday from DD11. Apple runs it's financial year from October. The company I used to work for ran from July. The standard tax year in the US is Jan-Dec, in the UK it's Apr-Mar. There is no such thing as a standard week 1. What would you want the software to do? Have a business auto detect function so it knows what week 1 would be? The best way of doing it is as Alan stated. This gives you the flexibility you need. After all businesses do move their year around. The business I mentioned earlier that ran from July, now runs from Jan. How would a built in function handle such a change?

Jim.
Struggling through the quagmire of life to reach the other side of who knows where.
Shop at Amazon
Jimbo PC Builds on YouTube
OS: Mac OS 11 PA Version: 2.0.7
declanr
MVP
Posts: 1815
Joined: Mon Dec 05, 2011 11:51 am
OLAP Product: Cognos TM1
Version: PA2.0 and most of the old ones
Excel Version: All of em
Location: Manchester, United Kingdom
Contact:

Re: Using Dates And Times In TM1

Post by declanr »

Beek,

Agree entirely with the guys above, my current customer has also changed the month its financial year ran from/to recently and it's not the first time I have come across such a thing so it definitely isn't uncommon - particularly in "younger" businesses or those that go through mergers etc.

I would point out that the majority of ledger systems have a "date" table somewhere in the back-end database, so if you have an ODBC connection to such a thing my preferred method is to point at the ledger system's date table and build a copy in a 2-d TM1 cube (I often prefix such cubes with a } so that they get hidden away) - you can then use the fields in here to build your date dimension and relevant roll ups.

Declan
Declan Rodger
beek
Posts: 63
Joined: Wed Sep 14, 2011 3:10 am
OLAP Product: TM1
Version: PA 2.0
Excel Version: Office 365

Re: Using Dates And Times In TM1

Post by beek »

I appreciates your inputs and yes, I totally agree with the point that financial period can starts from any month of the year. Let me explain, the reason why I'm asking about Week, is because I noticed some calendar do indicate the Week. Hence I thought Week is something standard, even when I google for calendar, I can see the Week indication, Example http://www.calendar-365.com/holidays/2013.html , try to click on the Download 2013 Calendar. The JPG or PDF will also reflect the Week number there.
Besides, I understand there's function in SAP GET_WEEK_INFO_BASED_ON_DATE , hence I'm wondering if anything like this can be found in TM1 :)
Anyway, after seeing Alan's reply yesterday, I've developed a simple TI process to build the Week information into Cube. :)
beek
Posts: 63
Joined: Wed Sep 14, 2011 3:10 am
OLAP Product: TM1
Version: PA 2.0
Excel Version: Office 365

Re: Using Dates And Times In TM1

Post by beek »

And.. to answer
jim wood wrote:What would you want the software to do? Have a business auto detect function so it knows what week 1 would be?
Our user wants to start to do forecasting in TM1 system. They are doing a weekly forecast. They will close the version weekly manually, if not, system will be closing for them during Weekend. Eg now in W22. They can only close up to version 22. They will not be able to close version 23, because the start date for w23 is 3rd June.
That's why I start to ask about the Week function. Anyway, by building Week cube (with start date), what the user wants can be achieved..
Darkhorse
Posts: 141
Joined: Wed Mar 09, 2011 1:25 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2003 2007 2010 2013

Re: Using Dates And Times In TM1

Post by Darkhorse »

Can some oen help me converting a time stamp like this:
20:48:31.027
into a time serial no:

thanks
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Using Dates And Times In TM1

Post by Alan Kirk »

Darkhorse wrote:Can some oen help me converting a time stamp like this:
20:48:31.027
into a time serial no:
As I mentioned in the original post there's really no such thing as a time component by itself; any such time without a date component really just takes you the start of 1960, the Don Draper suit is optional.

In some cases that's enough, however.

That having been said you should also be wary about relying on fractions of seconds like that. Although capable of storing values to that accuracy, as discussed in this thread the effective accuracy of TM1 processing is only 1 second.

In answer to your question:
- 1 is the value of a complete day;
- A day consists of 24 * 60 * 60 seconds which = 86,400 seconds;
- You can use text rules functions to parse your timestamp based on the colon (:) characters to get the hours, minutes and seconds components;
- You can then get the number of seconds by (in this case) (20 * 60 * 60 ) + (48 * 60 ) + 31.027
- Divide that value by 86,400 and you have the corresponding time component of a date serial number.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Darkhorse
Posts: 141
Joined: Wed Mar 09, 2011 1:25 pm
OLAP Product: TM1
Version: 10.2.2
Excel Version: 2003 2007 2010 2013

Re: Using Dates And Times In TM1

Post by Darkhorse »

Thanks Kirk as always highly informative, :D
beek
Posts: 63
Joined: Wed Sep 14, 2011 3:10 am
OLAP Product: TM1
Version: PA 2.0
Excel Version: Office 365

Re: Using Dates And Times In TM1

Post by beek »

Hi all,
tonester30 wrote:For instance in the Web when the user enters "01/01/2010" (US format) the format held in TM1 as originally input, but now when the user enteres "01/01/2010" in Excel it saves as "40179" in TM1.
Just to share, I've just encountered the same thing few minutes ago. It seem there is a 3rd way to overcome this, which is by inputting the date in YYYY-MMM format. It seem inputting date format starting with YYYY, excel will not convert it to number.
User avatar
Alan Kirk
Site Admin
Posts: 6606
Joined: Sun May 11, 2008 2:30 am
OLAP Product: TM1
Version: PA2.0.9.18 Classic NO PAW!
Excel Version: 2013 and Office 365
Location: Sydney, Australia
Contact:

Re: Using Dates And Times In TM1

Post by Alan Kirk »

I'm going to lock this thread. It was intended to be a quick reference about dates and times (hence its presence in "Useful Code, Tips and Tricks", not the main forum). However it seems to have started to attract "any question related to using dates and times in my own environment", which was not the intention. The most recent question posts (which were a lot more specific than general) have been moved out to the main forum.
"To them, equipment failure is terrifying. To me, it’s 'Tuesday.' "
-----------
Before posting, please check the documentation, the FAQ, the Search function and FOR THE LOVE OF GLUB the Request Guidelines.
Locked