topic Decimal time in whole seconds... in QlikView https://community.qlik.com/t5/QlikView/Decimal-time-in-whole-seconds/m-p/1333248#M411638 <HTML><HEAD></HEAD><BODY><P>With date/time fields, the approach is to break them up using something like</P><P></P><P>Date(dateandtime) for the date</P><P>frac(dateandtime) for the time</P><P></P><P>because for 10 years of transactions, there could be 315,000,000 distinct values for each date/time - wowzers batman!!</P><P></P><P>If the time is hours, minutes, seconds this solution will work but sometimes the field may include fractional seconds because internally the date/time is just a decimal number (date is offset from a base - usually 1/1900 and the time is simply seconds/86400).&nbsp; If you don't need the fractional part of the time there is any easy way to get rid of it.</P><P></P><P>1/86400 is approximately 0.000012 so you only need 6 decimal places for any second of the day.&nbsp; When breaking up the time use the following</P><P></P><P>FLOOR(FRAC(dateandtime)*86400)/86400</P><P></P><P>All it does it remove all the "junk" beyond the 6th decimal place and round to the nearest second.&nbsp; It will likely get your distinct values down considerably.</P></BODY></HTML> Thu, 27 Apr 2017 21:27:16 GMT markp201 2017-04-27T21:27:16Z Decimal time in whole seconds... https://community.qlik.com/t5/QlikView/Decimal-time-in-whole-seconds/m-p/1333248#M411638 <HTML><HEAD></HEAD><BODY><P>With date/time fields, the approach is to break them up using something like</P><P></P><P>Date(dateandtime) for the date</P><P>frac(dateandtime) for the time</P><P></P><P>because for 10 years of transactions, there could be 315,000,000 distinct values for each date/time - wowzers batman!!</P><P></P><P>If the time is hours, minutes, seconds this solution will work but sometimes the field may include fractional seconds because internally the date/time is just a decimal number (date is offset from a base - usually 1/1900 and the time is simply seconds/86400).&nbsp; If you don't need the fractional part of the time there is any easy way to get rid of it.</P><P></P><P>1/86400 is approximately 0.000012 so you only need 6 decimal places for any second of the day.&nbsp; When breaking up the time use the following</P><P></P><P>FLOOR(FRAC(dateandtime)*86400)/86400</P><P></P><P>All it does it remove all the "junk" beyond the 6th decimal place and round to the nearest second.&nbsp; It will likely get your distinct values down considerably.</P></BODY></HTML> Thu, 27 Apr 2017 21:27:16 GMT https://community.qlik.com/t5/QlikView/Decimal-time-in-whole-seconds/m-p/1333248#M411638 markp201 2017-04-27T21:27:16Z