As Sisense currently supports daily data, in the following article we will demonstrate how to include time in Sisense Web widgets that is present in DateTime fields.
- In order to display time format along with date we would Represent DateTime field as Text
- In order to filter by time format we would Represent DateTime field as BigInt
Represent DateTime As Text
- Add a custom Text field to your table
- To ensure that the text field is sorted in order, we will format it as 'yyyy-mm-dd HH:mm:ss'.
- If your datetime field is in the following format: 'yyyy-mm-dd HH:mm:ss', edit the field and insert the following script replacing "[A]" by your datetime field:
SubString(ToString([A]),0,IndexOf(ToString([A]),'.',1))
- If your datetime field is in another format, edit the field and insert the following script replacing "[A]" by your datetime field:
tostring(getyear([A]))
+ '-' +
(CASE WHEN GETMONTH([A])< 10 THEN '0' + tostring(GETMONTH([A])) ELSE tostring(GETMONTH([A])) END)
+ '-' +
(CASE WHEN GETDAY([A])< 10 THEN '0' + tostring(GETDAY([A])) ELSE tostring(GETDAY([A])) END)
+ ' ' +
(CASE WHEN GETHOUR([A])< 10 THEN '0' + tostring(GETHOUR([A])) ELSE tostring(GETHOUR([A])) END)
+ ':' +
(CASE WHEN GETMINUTE([A])< 10 THEN '0' + tostring(GETMINUTE([A])) ELSE tostring(GETMINUTE([A])) END)
+ ':' +
(CASE WHEN GETSECOND([A])< 10 THEN '0' + tostring(GETSECOND([A])) ELSE tostring(GETSECOND([A])) END)
- Build your ElastiCube, refresh Sisense Web
- Use this field to present the full DateTime format in a the x-axis of a line chart, rows of pivot table etc.
Represent DateTime As BigInt
- Add a custom BigInt field to your table
- Edit the field and insert the following script replacing "[A]" by your datetime field:
GETYEAR([A])*10000000000
+GETMONTH([A])*100000000
+GETDAY([A])*1000000
+GETHOUR([A])*10000
+GETMINUTE([A])*100
+GETSECOND([A])
- Build your ElastiCube, refresh Sisense Web
- Use this field to filter the original DateTime field ([A]) in your widget to latest date or otherwise.
