SiSense Analytics - Localization and DayOfWeek Correction

Follow

Santhosh Prabhakaran

July 12, 2017 06:03

Hi,

If you have installed SiSense Analytics, you may need to modify the following fields

ACTION_DAY (calculated field in table TRACE)

Replace existing formula to the below formula to get the right day of week - we found ours to be off by a big margin

ToString(GETDAY([TIMESTAMP])) + '. ' + DayName(DayOfWeek([TIMESTAMP]))

 

TIMESTAMP (calculated field in table TRACE)

If you fall in a timezone which has decimal point difference in hours from UTC, please use AddMinutes as AddHours only takes integer values and disregards whatever is after the decimal - does not throw an error if you have decimals.  For e.g. if you are in India, use the following formula

AddMinutes(AddHours(todatetime(SubString([timestamp_old],0,10) + ' ' + SubString([timestamp_old],12,8)),5),30)

 

Best Regards,

Santhosh Kumar Prabhakaran

 

1

• 

  Oxana Noa Umansky July 12, 2017 13:04

  Thanks for sharing Santhosh! What Sisense version are you using? 

  0

  Comment actions Permalink
• 

  Santhosh Prabhakaran July 12, 2017 15:06 (Edited July 12, 2017 15:07)

  Hi Oxana,

  I am using 6.6.1.11003.

  The following will help you get sortable days

  ifstring (length(ToString(GETDAY([TIMESTAMP])))=1, '0'+ToString(GETDAY([TIMESTAMP])), ToString(GETDAY([TIMESTAMP]))) + '. ' + DayName(DayOfWeek([TIMESTAMP]))

   

   

  I found it useful to use the following to get sortable time

   

  ifstring (length(tostring(gethour([TIMESTAMP])))=1, '0'+tostring(gethour([TIMESTAMP])), tostring(gethour([TIMESTAMP])))+':'+ifstring (length(tostring(getminute([TIMESTAMP])))=1, '0'+tostring(getminute([TIMESTAMP])), tostring(getminute([TIMESTAMP])))+':'+ifstring (length(tostring(getsecond([TIMESTAMP])))=1, '0'+tostring(getsecond([TIMESTAMP])), tostring(getsecond([TIMESTAMP])))

   

  Thanks,

  Santhosh

   

  0

  Comment actions Permalink

Please sign in to leave a comment.