Planning Analytics

Hey everyone,

I could use some help with date and time display formats in PAW, specifically when working with the NOW() function. Here's the situation:

1. NOW() Function Usage: In one of my TI processes, I'm using NOW() to capture the numerical date/time and storing it in a TM1 dimension member. For example:

   ATTRPUTN(NOW, 'Import_Log', Import_Log, 'EndTimeN')

2. Display Format Issue: Following that, I'm attempting to set the display format (within PAW) for the date and time. I've set the format for 'EndTimeN' like this: d:m/d/yyyy h:nn:ss ID|DT|S|M|Y|Y.

   Just to note, the format string includes a special character after 'h:nn:ss'. 
   However, despite this setup, I'm encountering a problem. 
   Instead of displaying something like 10.04.2024 13:52:46, I'm getting 10.04.1964 13:52:46
   when the numerical date is 23477.57831018519.

Seeking Advice: Any suggestions on how to resolve this discrepancy in the date and time display format?

Additional Info: I've tried consulting the IBM manual for Date and Time display formats (link), but without success. While I did receive some help experimenting with date and time display formats in the Performance Modeller, I generally don't use that product.

I'd appreciate any insights or suggestions you might have. Thanks for your assistance!

Cheers, Asgeir

------------------------------
Asgeir Thorgeirsson
------------------------------

Hey everyone,

I could use some help with date and time display formats in PAW, specifically when working with the NOW() function. Here's the situation:

1. NOW() Function Usage: In one of my TI processes, I'm using NOW() to capture the numerical date/time and storing it in a TM1 dimension member. For example:

   ATTRPUTN(NOW, 'Import_Log', Import_Log, 'EndTimeN')

2. Display Format Issue: Following that, I'm attempting to set the display format (within PAW) for the date and time. I've set the format for 'EndTimeN' like this: d:m/d/yyyy h:nn:ss ID|DT|S|M|Y|Y.

   Just to note, the format string includes a special character after 'h:nn:ss'. 
   However, despite this setup, I'm encountering a problem. 
   Instead of displaying something like 10.04.2024 13:52:46, I'm getting 10.04.1964 13:52:46
   when the numerical date is 23477.57831018519.

Seeking Advice: Any suggestions on how to resolve this discrepancy in the date and time display format?

Additional Info: I've tried consulting the IBM manual for Date and Time display formats (link), but without success. While I did receive some help experimenting with date and time display formats in the Performance Modeller, I generally don't use that product.

I'd appreciate any insights or suggestions you might have. Thanks for your assistance!

Cheers, Asgeir

------------------------------
Asgeir Thorgeirsson
------------------------------

Thank you @George Tonkin

The mind-boggling part is that when you select a date with the date selector or type in a date in PAW, it is stored with the 1900 base. So you also have to subtract 21915 from the selected date if you want to work with that date in TM1 rules or a TI process. So I am saying there is a conflict within TM1/PAW because it uses both the 1900 base and the 1960 base as default. Am I right or is this a misunderstanding?

Cheers, Asgier

Hey everyone,

I could use some help with date and time display formats in PAW, specifically when working with the NOW() function. Here's the situation:

1. NOW() Function Usage: In one of my TI processes, I'm using NOW() to capture the numerical date/time and storing it in a TM1 dimension member. For example:

   ATTRPUTN(NOW, 'Import_Log', Import_Log, 'EndTimeN')

2. Display Format Issue: Following that, I'm attempting to set the display format (within PAW) for the date and time. I've set the format for 'EndTimeN' like this: d:m/d/yyyy h:nn:ss ID|DT|S|M|Y|Y.

   Just to note, the format string includes a special character after 'h:nn:ss'. 
   However, despite this setup, I'm encountering a problem. 
   Instead of displaying something like 10.04.2024 13:52:46, I'm getting 10.04.1964 13:52:46
   when the numerical date is 23477.57831018519.

Seeking Advice: Any suggestions on how to resolve this discrepancy in the date and time display format?

Additional Info: I've tried consulting the IBM manual for Date and Time display formats (link), but without success. While I did receive some help experimenting with date and time display formats in the Performance Modeller, I generally don't use that product.

I'd appreciate any insights or suggestions you might have. Thanks for your assistance!

Cheers, Asgeir

------------------------------
Asgeir Thorgeirsson
------------------------------

Hi Asgeir,

If the behaviour is different, I would suggest you log this with IBM as they may not be aware.

The functionality should work consistently, whether it works consistently as you need it or not will be the next issue.

Thank you @George Tonkin

The mind-boggling part is that when you select a date with the date selector or type in a date in PAW, it is stored with the 1900 base. So you also have to subtract 21915 from the selected date if you want to work with that date in TM1 rules or a TI process. So I am saying there is a conflict within TM1/PAW because it uses both the 1900 base and the 1960 base as default. Am I right or is this a misunderstanding?

Cheers, Asgier

Hey everyone,

I could use some help with date and time display formats in PAW, specifically when working with the NOW() function. Here's the situation:

1. NOW() Function Usage: In one of my TI processes, I'm using NOW() to capture the numerical date/time and storing it in a TM1 dimension member. For example:

   ATTRPUTN(NOW, 'Import_Log', Import_Log, 'EndTimeN')

2. Display Format Issue: Following that, I'm attempting to set the display format (within PAW) for the date and time. I've set the format for 'EndTimeN' like this: d:m/d/yyyy h:nn:ss ID|DT|S|M|Y|Y.

   Just to note, the format string includes a special character after 'h:nn:ss'. 
   However, despite this setup, I'm encountering a problem. 
   Instead of displaying something like 10.04.2024 13:52:46, I'm getting 10.04.1964 13:52:46
   when the numerical date is 23477.57831018519.

Seeking Advice: Any suggestions on how to resolve this discrepancy in the date and time display format?

Additional Info: I've tried consulting the IBM manual for Date and Time display formats (link), but without success. While I did receive some help experimenting with date and time display formats in the Performance Modeller, I generally don't use that product.

I'd appreciate any insights or suggestions you might have. Thanks for your assistance!

Cheers, Asgeir

------------------------------
Asgeir Thorgeirsson
------------------------------

Hi George,

Given the fact that TM1 predates Excel I'd argue we'd have to log this with Microsoft <LOL>

Kidding aside, it's annoyed me in the past as well but my suggestion to change this couldn't count on much support as it would potentially be breaking customers in ways we can predict (not knowing how people use it). But it is the customer that assigns a meaning to an otherwise numeric value in a cube.

Open for suggestions to 'fix' this, short of me introducing types to tackle this once and for all ;-)

Cheers,

-H

PS wouldn't mind the ticket to spike the discussion on the topic once again (and otherwise make at least support aware of this magic 21915 value, which I'm sure they'd find in more support case;-).

------------------------------
Hubert Heijkers
STSM, Program Director TM1 Functional Database Technology and OData Evangelist

Original Message:
Sent: Fri April 12, 2024 01:21 AM
From: George Tonkin
Subject: Seeking Advice on Date and Time Display Formats

Hi Asgeir,

If the behaviour is different, I would suggest you log this with IBM as they may not be aware.

The functionality should work consistently, whether it works consistently as you need it or not will be the next issue.

------------------------------
George Tonkin
Business Partner
MCI Consultants
Johannesburg

Original Message:
Sent: Thu April 11, 2024 01:27 PM
From: Asgeir Thorgeirsson
Subject: Seeking Advice on Date and Time Display Formats

Thank you @George Tonkin

The mind-boggling part is that when you select a date with the date selector or type in a date in PAW, it is stored with the 1900 base. So you also have to subtract 21915 from the selected date if you want to work with that date in TM1 rules or a TI process. So I am saying there is a conflict within TM1/PAW because it uses both the 1900 base and the 1960 base as default. Am I right or is this a misunderstanding?

Cheers, Asgier

------------------------------
Asgeir Thorgeirsson

Original Message:
Sent: Thu April 11, 2024 10:49 AM
From: Asgeir Thorgeirsson
Subject: Seeking Advice on Date and Time Display Formats

Hey everyone,

I could use some help with date and time display formats in PAW, specifically when working with the NOW() function. Here's the situation:

1. NOW() Function Usage: In one of my TI processes, I'm using NOW() to capture the numerical date/time and storing it in a TM1 dimension member. For example:

   ATTRPUTN(NOW, 'Import_Log', Import_Log, 'EndTimeN')
   

2. Display Format Issue: Following that, I'm attempting to set the display format (within PAW) for the date and time. I've set the format for 'EndTimeN' like this: d:m/d/yyyy h:nn:ss ID|DT|S|M|Y|Y.

   Just to note, the format string includes a special character after 'h:nn:ss'. 
   However, despite this setup, I'm encountering a problem. 
   Instead of displaying something like 10.04.2024 13:52:46, I'm getting 10.04.1964 13:52:46
   when the numerical date is 23477.57831018519.

Seeking Advice: Any suggestions on how to resolve this discrepancy in the date and time display format?

Additional Info: I've tried consulting the IBM manual for Date and Time display formats (link), but without success. While I did receive some help experimenting with date and time display formats in the Performance Modeller, I generally don't use that product.

I'd appreciate any insights or suggestions you might have. Thanks for your assistance!

Cheers, Asgeir

------------------------------
Asgeir Thorgeirsson
------------------------------

Hi George,

Given the fact that TM1 predates Excel I'd argue we'd have to log this with Microsoft <LOL>

Kidding aside, it's annoyed me in the past as well but my suggestion to change this couldn't count on much support as it would potentially be breaking customers in ways we can predict (not knowing how people use it). But it is the customer that assigns a meaning to an otherwise numeric value in a cube.

Open for suggestions to 'fix' this, short of me introducing types to tackle this once and for all ;-)

Cheers,

-H

PS wouldn't mind the ticket to spike the discussion on the topic once again (and otherwise make at least support aware of this magic 21915 value, which I'm sure they'd find in more support case;-).

Hi Asgeir,

If the behaviour is different, I would suggest you log this with IBM as they may not be aware.

The functionality should work consistently, whether it works consistently as you need it or not will be the next issue.

Thank you @George Tonkin

The mind-boggling part is that when you select a date with the date selector or type in a date in PAW, it is stored with the 1900 base. So you also have to subtract 21915 from the selected date if you want to work with that date in TM1 rules or a TI process. So I am saying there is a conflict within TM1/PAW because it uses both the 1900 base and the 1960 base as default. Am I right or is this a misunderstanding?

Cheers, Asgier

Hey everyone,

I could use some help with date and time display formats in PAW, specifically when working with the NOW() function. Here's the situation:

1. NOW() Function Usage: In one of my TI processes, I'm using NOW() to capture the numerical date/time and storing it in a TM1 dimension member. For example:

   ATTRPUTN(NOW, 'Import_Log', Import_Log, 'EndTimeN')

2. Display Format Issue: Following that, I'm attempting to set the display format (within PAW) for the date and time. I've set the format for 'EndTimeN' like this: d:m/d/yyyy h:nn:ss ID|DT|S|M|Y|Y.

   Just to note, the format string includes a special character after 'h:nn:ss'. 
   However, despite this setup, I'm encountering a problem. 
   Instead of displaying something like 10.04.2024 13:52:46, I'm getting 10.04.1964 13:52:46
   when the numerical date is 23477.57831018519.

Seeking Advice: Any suggestions on how to resolve this discrepancy in the date and time display format?

Additional Info: I've tried consulting the IBM manual for Date and Time display formats (link), but without success. While I did receive some help experimenting with date and time display formats in the Performance Modeller, I generally don't use that product.

I'd appreciate any insights or suggestions you might have. Thanks for your assistance!

Cheers, Asgeir

------------------------------
Asgeir Thorgeirsson
------------------------------

This cfg helps, but is better if you get this setup when you start building. https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=file-useexcelserialdate

Hi George,

Given the fact that TM1 predates Excel I'd argue we'd have to log this with Microsoft <LOL>

Kidding aside, it's annoyed me in the past as well but my suggestion to change this couldn't count on much support as it would potentially be breaking customers in ways we can predict (not knowing how people use it). But it is the customer that assigns a meaning to an otherwise numeric value in a cube.

Open for suggestions to 'fix' this, short of me introducing types to tackle this once and for all ;-)

Cheers,

-H

PS wouldn't mind the ticket to spike the discussion on the topic once again (and otherwise make at least support aware of this magic 21915 value, which I'm sure they'd find in more support case;-).

Hi Asgeir,

If the behaviour is different, I would suggest you log this with IBM as they may not be aware.

The functionality should work consistently, whether it works consistently as you need it or not will be the next issue.

Thank you @George Tonkin

The mind-boggling part is that when you select a date with the date selector or type in a date in PAW, it is stored with the 1900 base. So you also have to subtract 21915 from the selected date if you want to work with that date in TM1 rules or a TI process. So I am saying there is a conflict within TM1/PAW because it uses both the 1900 base and the 1960 base as default. Am I right or is this a misunderstanding?

Cheers, Asgier

Hey everyone,

I could use some help with date and time display formats in PAW, specifically when working with the NOW() function. Here's the situation:

1. NOW() Function Usage: In one of my TI processes, I'm using NOW() to capture the numerical date/time and storing it in a TM1 dimension member. For example:

   ATTRPUTN(NOW, 'Import_Log', Import_Log, 'EndTimeN')

2. Display Format Issue: Following that, I'm attempting to set the display format (within PAW) for the date and time. I've set the format for 'EndTimeN' like this: d:m/d/yyyy h:nn:ss ID|DT|S|M|Y|Y.

   Just to note, the format string includes a special character after 'h:nn:ss'. 
   However, despite this setup, I'm encountering a problem. 
   Instead of displaying something like 10.04.2024 13:52:46, I'm getting 10.04.1964 13:52:46
   when the numerical date is 23477.57831018519.

Seeking Advice: Any suggestions on how to resolve this discrepancy in the date and time display format?

Additional Info: I've tried consulting the IBM manual for Date and Time display formats (link), but without success. While I did receive some help experimenting with date and time display formats in the Performance Modeller, I generally don't use that product.

I'd appreciate any insights or suggestions you might have. Thanks for your assistance!

Cheers, Asgeir

------------------------------
Asgeir Thorgeirsson
------------------------------

Thanks for reminding me about that setting Ryan, forgot about that when I responded earlier. This was actually the trigger for the discussion I had internally when we were cleaning up configuration parameters in v12 but, as you can imagine, removing it and always using Excel's serial date would have broken things since there is no way for TM1 to know what numbers in a cube represent a date.

However I'm thinking there wouldn't be anything wrong with making this setting default to true for new database going forward now would it?

This cfg helps, but is better if you get this setup when you start building. https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=file-useexcelserialdate

Hi George,

Given the fact that TM1 predates Excel I'd argue we'd have to log this with Microsoft <LOL>

Kidding aside, it's annoyed me in the past as well but my suggestion to change this couldn't count on much support as it would potentially be breaking customers in ways we can predict (not knowing how people use it). But it is the customer that assigns a meaning to an otherwise numeric value in a cube.

Open for suggestions to 'fix' this, short of me introducing types to tackle this once and for all ;-)

Cheers,

-H

PS wouldn't mind the ticket to spike the discussion on the topic once again (and otherwise make at least support aware of this magic 21915 value, which I'm sure they'd find in more support case;-).

Hi Asgeir,

If the behaviour is different, I would suggest you log this with IBM as they may not be aware.

The functionality should work consistently, whether it works consistently as you need it or not will be the next issue.

Thank you @George Tonkin

The mind-boggling part is that when you select a date with the date selector or type in a date in PAW, it is stored with the 1900 base. So you also have to subtract 21915 from the selected date if you want to work with that date in TM1 rules or a TI process. So I am saying there is a conflict within TM1/PAW because it uses both the 1900 base and the 1960 base as default. Am I right or is this a misunderstanding?

Cheers, Asgier

Hey everyone,

I could use some help with date and time display formats in PAW, specifically when working with the NOW() function. Here's the situation:

1. NOW() Function Usage: In one of my TI processes, I'm using NOW() to capture the numerical date/time and storing it in a TM1 dimension member. For example:

   ATTRPUTN(NOW, 'Import_Log', Import_Log, 'EndTimeN')

2. Display Format Issue: Following that, I'm attempting to set the display format (within PAW) for the date and time. I've set the format for 'EndTimeN' like this: d:m/d/yyyy h:nn:ss ID|DT|S|M|Y|Y.

   Just to note, the format string includes a special character after 'h:nn:ss'. 
   However, despite this setup, I'm encountering a problem. 
   Instead of displaying something like 10.04.2024 13:52:46, I'm getting 10.04.1964 13:52:46
   when the numerical date is 23477.57831018519.

Seeking Advice: Any suggestions on how to resolve this discrepancy in the date and time display format?

Additional Info: I've tried consulting the IBM manual for Date and Time display formats (link), but without success. While I did receive some help experimenting with date and time display formats in the Performance Modeller, I generally don't use that product.

I'd appreciate any insights or suggestions you might have. Thanks for your assistance!

Cheers, Asgeir

------------------------------
Asgeir Thorgeirsson
------------------------------

For me it can be T as the default value. It is 1 (small) step less when starting a new project.

Thanks for reminding me about that setting Ryan, forgot about that when I responded earlier. This was actually the trigger for the discussion I had internally when we were cleaning up configuration parameters in v12 but, as you can imagine, removing it and always using Excel's serial date would have broken things since there is no way for TM1 to know what numbers in a cube represent a date.

However I'm thinking there wouldn't be anything wrong with making this setting default to true for new database going forward now would it?

This cfg helps, but is better if you get this setup when you start building. https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=file-useexcelserialdate

Hi George,

Given the fact that TM1 predates Excel I'd argue we'd have to log this with Microsoft <LOL>

Kidding aside, it's annoyed me in the past as well but my suggestion to change this couldn't count on much support as it would potentially be breaking customers in ways we can predict (not knowing how people use it). But it is the customer that assigns a meaning to an otherwise numeric value in a cube.

Open for suggestions to 'fix' this, short of me introducing types to tackle this once and for all ;-)

Cheers,

-H

PS wouldn't mind the ticket to spike the discussion on the topic once again (and otherwise make at least support aware of this magic 21915 value, which I'm sure they'd find in more support case;-).

Hi Asgeir,

If the behaviour is different, I would suggest you log this with IBM as they may not be aware.

The functionality should work consistently, whether it works consistently as you need it or not will be the next issue.

Thank you @George Tonkin

The mind-boggling part is that when you select a date with the date selector or type in a date in PAW, it is stored with the 1900 base. So you also have to subtract 21915 from the selected date if you want to work with that date in TM1 rules or a TI process. So I am saying there is a conflict within TM1/PAW because it uses both the 1900 base and the 1960 base as default. Am I right or is this a misunderstanding?

Cheers, Asgier

Hey everyone,

I could use some help with date and time display formats in PAW, specifically when working with the NOW() function. Here's the situation:

1. NOW() Function Usage: In one of my TI processes, I'm using NOW() to capture the numerical date/time and storing it in a TM1 dimension member. For example:

   ATTRPUTN(NOW, 'Import_Log', Import_Log, 'EndTimeN')

2. Display Format Issue: Following that, I'm attempting to set the display format (within PAW) for the date and time. I've set the format for 'EndTimeN' like this: d:m/d/yyyy h:nn:ss ID|DT|S|M|Y|Y.

   Just to note, the format string includes a special character after 'h:nn:ss'. 
   However, despite this setup, I'm encountering a problem. 
   Instead of displaying something like 10.04.2024 13:52:46, I'm getting 10.04.1964 13:52:46
   when the numerical date is 23477.57831018519.

Seeking Advice: Any suggestions on how to resolve this discrepancy in the date and time display format?

Additional Info: I've tried consulting the IBM manual for Date and Time display formats (link), but without success. While I did receive some help experimenting with date and time display formats in the Performance Modeller, I generally don't use that product.

I'd appreciate any insights or suggestions you might have. Thanks for your assistance!

Cheers, Asgeir

------------------------------
Asgeir Thorgeirsson
------------------------------

Thanks for reminding me about that setting Ryan, forgot about that when I responded earlier. This was actually the trigger for the discussion I had internally when we were cleaning up configuration parameters in v12 but, as you can imagine, removing it and always using Excel's serial date would have broken things since there is no way for TM1 to know what numbers in a cube represent a date.

However I'm thinking there wouldn't be anything wrong with making this setting default to true for new database going forward now would it?

This cfg helps, but is better if you get this setup when you start building. https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=file-useexcelserialdate

Hi George,

Given the fact that TM1 predates Excel I'd argue we'd have to log this with Microsoft <LOL>

Kidding aside, it's annoyed me in the past as well but my suggestion to change this couldn't count on much support as it would potentially be breaking customers in ways we can predict (not knowing how people use it). But it is the customer that assigns a meaning to an otherwise numeric value in a cube.

Open for suggestions to 'fix' this, short of me introducing types to tackle this once and for all ;-)

Cheers,

-H

PS wouldn't mind the ticket to spike the discussion on the topic once again (and otherwise make at least support aware of this magic 21915 value, which I'm sure they'd find in more support case;-).

Hi Asgeir,

If the behaviour is different, I would suggest you log this with IBM as they may not be aware.

The functionality should work consistently, whether it works consistently as you need it or not will be the next issue.

Thank you @George Tonkin

The mind-boggling part is that when you select a date with the date selector or type in a date in PAW, it is stored with the 1900 base. So you also have to subtract 21915 from the selected date if you want to work with that date in TM1 rules or a TI process. So I am saying there is a conflict within TM1/PAW because it uses both the 1900 base and the 1960 base as default. Am I right or is this a misunderstanding?

Cheers, Asgier

Hey everyone,

I could use some help with date and time display formats in PAW, specifically when working with the NOW() function. Here's the situation:

1. NOW() Function Usage: In one of my TI processes, I'm using NOW() to capture the numerical date/time and storing it in a TM1 dimension member. For example:

   ATTRPUTN(NOW, 'Import_Log', Import_Log, 'EndTimeN')

2. Display Format Issue: Following that, I'm attempting to set the display format (within PAW) for the date and time. I've set the format for 'EndTimeN' like this: d:m/d/yyyy h:nn:ss ID|DT|S|M|Y|Y.

   Just to note, the format string includes a special character after 'h:nn:ss'. 
   However, despite this setup, I'm encountering a problem. 
   Instead of displaying something like 10.04.2024 13:52:46, I'm getting 10.04.1964 13:52:46
   when the numerical date is 23477.57831018519.

Seeking Advice: Any suggestions on how to resolve this discrepancy in the date and time display format?

Additional Info: I've tried consulting the IBM manual for Date and Time display formats (link), but without success. While I did receive some help experimenting with date and time display formats in the Performance Modeller, I generally don't use that product.

I'd appreciate any insights or suggestions you might have. Thanks for your assistance!

Cheers, Asgeir

------------------------------
Asgeir Thorgeirsson
------------------------------

I agree, defaulting to true would be good. One question though, does the v12 backup functionality also capture the config settings? If so, then changing the default is the way to go. 

Thanks for reminding me about that setting Ryan, forgot about that when I responded earlier. This was actually the trigger for the discussion I had internally when we were cleaning up configuration parameters in v12 but, as you can imagine, removing it and always using Excel's serial date would have broken things since there is no way for TM1 to know what numbers in a cube represent a date.

However I'm thinking there wouldn't be anything wrong with making this setting default to true for new database going forward now would it?

This cfg helps, but is better if you get this setup when you start building. https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=file-useexcelserialdate

Hi George,

Given the fact that TM1 predates Excel I'd argue we'd have to log this with Microsoft <LOL>

Kidding aside, it's annoyed me in the past as well but my suggestion to change this couldn't count on much support as it would potentially be breaking customers in ways we can predict (not knowing how people use it). But it is the customer that assigns a meaning to an otherwise numeric value in a cube.

Open for suggestions to 'fix' this, short of me introducing types to tackle this once and for all ;-)

Cheers,

-H

PS wouldn't mind the ticket to spike the discussion on the topic once again (and otherwise make at least support aware of this magic 21915 value, which I'm sure they'd find in more support case;-).

Hi Asgeir,

If the behaviour is different, I would suggest you log this with IBM as they may not be aware.

The functionality should work consistently, whether it works consistently as you need it or not will be the next issue.

Thank you @George Tonkin

The mind-boggling part is that when you select a date with the date selector or type in a date in PAW, it is stored with the 1900 base. So you also have to subtract 21915 from the selected date if you want to work with that date in TM1 rules or a TI process. So I am saying there is a conflict within TM1/PAW because it uses both the 1900 base and the 1960 base as default. Am I right or is this a misunderstanding?

Cheers, Asgier

Hey everyone,

I could use some help with date and time display formats in PAW, specifically when working with the NOW() function. Here's the situation:

1. NOW() Function Usage: In one of my TI processes, I'm using NOW() to capture the numerical date/time and storing it in a TM1 dimension member. For example:

   ATTRPUTN(NOW, 'Import_Log', Import_Log, 'EndTimeN')

2. Display Format Issue: Following that, I'm attempting to set the display format (within PAW) for the date and time. I've set the format for 'EndTimeN' like this: d:m/d/yyyy h:nn:ss ID|DT|S|M|Y|Y.

   Just to note, the format string includes a special character after 'h:nn:ss'. 
   However, despite this setup, I'm encountering a problem. 
   Instead of displaying something like 10.04.2024 13:52:46, I'm getting 10.04.1964 13:52:46
   when the numerical date is 23477.57831018519.

Seeking Advice: Any suggestions on how to resolve this discrepancy in the date and time display format?

Additional Info: I've tried consulting the IBM manual for Date and Time display formats (link), but without success. While I did receive some help experimenting with date and time display formats in the Performance Modeller, I generally don't use that product.

I'd appreciate any insights or suggestions you might have. Thanks for your assistance!

Cheers, Asgeir

------------------------------
Asgeir Thorgeirsson
------------------------------

Thanks for reminding me about that setting Ryan, forgot about that when I responded earlier. This was actually the trigger for the discussion I had internally when we were cleaning up configuration parameters in v12 but, as you can imagine, removing it and always using Excel's serial date would have broken things since there is no way for TM1 to know what numbers in a cube represent a date.

However I'm thinking there wouldn't be anything wrong with making this setting default to true for new database going forward now would it?

This cfg helps, but is better if you get this setup when you start building. https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=file-useexcelserialdate

Hi George,

Given the fact that TM1 predates Excel I'd argue we'd have to log this with Microsoft <LOL>

Kidding aside, it's annoyed me in the past as well but my suggestion to change this couldn't count on much support as it would potentially be breaking customers in ways we can predict (not knowing how people use it). But it is the customer that assigns a meaning to an otherwise numeric value in a cube.

Open for suggestions to 'fix' this, short of me introducing types to tackle this once and for all ;-)

Cheers,

-H

PS wouldn't mind the ticket to spike the discussion on the topic once again (and otherwise make at least support aware of this magic 21915 value, which I'm sure they'd find in more support case;-).

Hi Asgeir,

If the behaviour is different, I would suggest you log this with IBM as they may not be aware.

The functionality should work consistently, whether it works consistently as you need it or not will be the next issue.

Thank you @George Tonkin

The mind-boggling part is that when you select a date with the date selector or type in a date in PAW, it is stored with the 1900 base. So you also have to subtract 21915 from the selected date if you want to work with that date in TM1 rules or a TI process. So I am saying there is a conflict within TM1/PAW because it uses both the 1900 base and the 1960 base as default. Am I right or is this a misunderstanding?

Cheers, Asgier

Hey everyone,

I could use some help with date and time display formats in PAW, specifically when working with the NOW() function. Here's the situation:

1. NOW() Function Usage: In one of my TI processes, I'm using NOW() to capture the numerical date/time and storing it in a TM1 dimension member. For example:

   ATTRPUTN(NOW, 'Import_Log', Import_Log, 'EndTimeN')

2. Display Format Issue: Following that, I'm attempting to set the display format (within PAW) for the date and time. I've set the format for 'EndTimeN' like this: d:m/d/yyyy h:nn:ss ID|DT|S|M|Y|Y.

   Just to note, the format string includes a special character after 'h:nn:ss'. 
   However, despite this setup, I'm encountering a problem. 
   Instead of displaying something like 10.04.2024 13:52:46, I'm getting 10.04.1964 13:52:46
   when the numerical date is 23477.57831018519.

Seeking Advice: Any suggestions on how to resolve this discrepancy in the date and time display format?

Additional Info: I've tried consulting the IBM manual for Date and Time display formats (link), but without success. While I did receive some help experimenting with date and time display formats in the Performance Modeller, I generally don't use that product.

I'd appreciate any insights or suggestions you might have. Thanks for your assistance!

Cheers, Asgeir

------------------------------
Asgeir Thorgeirsson
------------------------------

+1 for default to True from my side - I am setting this on new models but a default would be good!

Would also need to see how changing the default in the future impacts existing models e.g. running on PAoC where the configuration uses the defaults and is currently false. A change could have unintended consequences for some if the flag is not first written back as False.

Thanks for reminding me about that setting Ryan, forgot about that when I responded earlier. This was actually the trigger for the discussion I had internally when we were cleaning up configuration parameters in v12 but, as you can imagine, removing it and always using Excel's serial date would have broken things since there is no way for TM1 to know what numbers in a cube represent a date.

However I'm thinking there wouldn't be anything wrong with making this setting default to true for new database going forward now would it?

This cfg helps, but is better if you get this setup when you start building. https://www.ibm.com/docs/en/planning-analytics/2.0.0?topic=file-useexcelserialdate

Hi George,

Given the fact that TM1 predates Excel I'd argue we'd have to log this with Microsoft <LOL>

Kidding aside, it's annoyed me in the past as well but my suggestion to change this couldn't count on much support as it would potentially be breaking customers in ways we can predict (not knowing how people use it). But it is the customer that assigns a meaning to an otherwise numeric value in a cube.

Open for suggestions to 'fix' this, short of me introducing types to tackle this once and for all ;-)

Cheers,

-H

PS wouldn't mind the ticket to spike the discussion on the topic once again (and otherwise make at least support aware of this magic 21915 value, which I'm sure they'd find in more support case;-).

Hi Asgeir,

If the behaviour is different, I would suggest you log this with IBM as they may not be aware.

The functionality should work consistently, whether it works consistently as you need it or not will be the next issue.

Thank you @George Tonkin

The mind-boggling part is that when you select a date with the date selector or type in a date in PAW, it is stored with the 1900 base. So you also have to subtract 21915 from the selected date if you want to work with that date in TM1 rules or a TI process. So I am saying there is a conflict within TM1/PAW because it uses both the 1900 base and the 1960 base as default. Am I right or is this a misunderstanding?

Cheers, Asgier

Hey everyone,

I could use some help with date and time display formats in PAW, specifically when working with the NOW() function. Here's the situation:

1. NOW() Function Usage: In one of my TI processes, I'm using NOW() to capture the numerical date/time and storing it in a TM1 dimension member. For example:

   ATTRPUTN(NOW, 'Import_Log', Import_Log, 'EndTimeN')

2. Display Format Issue: Following that, I'm attempting to set the display format (within PAW) for the date and time. I've set the format for 'EndTimeN' like this: d:m/d/yyyy h:nn:ss ID|DT|S|M|Y|Y.

   Just to note, the format string includes a special character after 'h:nn:ss'. 
   However, despite this setup, I'm encountering a problem. 
   Instead of displaying something like 10.04.2024 13:52:46, I'm getting 10.04.1964 13:52:46
   when the numerical date is 23477.57831018519.

Seeking Advice: Any suggestions on how to resolve this discrepancy in the date and time display format?

Additional Info: I've tried consulting the IBM manual for Date and Time display formats (link), but without success. While I did receive some help experimenting with date and time display formats in the Performance Modeller, I generally don't use that product.

I'd appreciate any insights or suggestions you might have. Thanks for your assistance!

Cheers, Asgeir

------------------------------
Asgeir Thorgeirsson
------------------------------