topic Re: Convert Milliseconds to Seconds in QlikView

Convert Milliseconds to Seconds

puneetagarwal — Wed, 25 Nov 2020 16:16:04 GMT

Hi All,

We have a column in db where the values gets store in Milliseconds (Bigint), I need to covert the Milliseconds value into seconds in qlikview.

When we export the Data/Excel we should be able to perform Aggregate calculation (Sum,Avg) etc.

Sample Values:

TimeInMS	Time in Seconds
420000	420
2640000	2640
420000	420
120000	120
420000	420

Regards,

Puneet Agarwal

Re: Convert Milliseconds to Seconds

Chanty4u — Thu, 02 Aug 2018 11:08:44 GMT

try this

=round(Yourfield/1000)

Re: Convert Milliseconds to Seconds

puneetagarwal — Thu, 02 Aug 2018 11:10:46 GMT

Tried, when i export to excel im not able to perform calculation on this column.

That is my major concern.

Re: Convert Milliseconds to Seconds

BalaBhaskar_Qlik — Thu, 02 Aug 2018 11:15:26 GMT

=round(TimeinMS/1000) as [Time in Seconds]

=interval(round(YourMillisecondsField/1000)/(60*60*24),'hh:mm:ss') as YourNewField

Re: Convert Milliseconds to Seconds

Chanty4u — Thu, 02 Aug 2018 11:16:28 GMT

try this

interval(round(YourMillisecondsField/1000)/(60*60*24),'hh:mm:ss') as YourNewField

Re: Convert Milliseconds to Seconds

sunny_talwar — Thu, 02 Aug 2018 11:18:02 GMT

Is TimeInMs a number or an actual time field?

Re: Convert Milliseconds to Seconds

puneetagarwal — Thu, 02 Aug 2018 11:23:26 GMT

Its a number stored in db (datatype: Bigint)

Re: Convert Milliseconds to Seconds

puneetagarwal — Thu, 02 Aug 2018 11:24:58 GMT

Tried this function , i was successfully able to convert into seconds.

But when i export to excel i am not able to perform the calculation on that column (Avg,Sum) etc.

Re: Convert Milliseconds to Seconds

sunny_talwar — Thu, 02 Aug 2018 11:26:19 GMT

I guess you got what you need... but this could be the other way to do this

=Interval(Interval#(TimeInMS/1000, 'ss'), 'ss')

Re: Convert Milliseconds to Seconds

puneetagarwal — Thu, 02 Aug 2018 11:26:54 GMT

Tried this function , i was successfully able to convert into seconds.

But when i export to excel i am not able to perform the calculation on that column (Avg,Sum) etc.

Note: The user wants to perform the mathematical calculation after exporting the excel on this column.

Re: Convert Milliseconds to Seconds

sunny_talwar — Thu, 02 Aug 2018 11:28:50 GMT

what was going wrong when it was exported? it was not a number?

Re: Convert Milliseconds to Seconds

puneetagarwal — Thu, 02 Aug 2018 11:29:15 GMT

Let me explain you in detail.

Suppose after exporting the values we have values like

50
30
10
90
15
If we want to sum for the above values it should give the result as 195. But it is giving as 0.

Hope it explains

Re: Convert Milliseconds to Seconds

puneetagarwal — Thu, 02 Aug 2018 11:31:16 GMT

Yes, Its a number. but,after exporting also we see the number but with a Green asking me to convert that values to number to perform calculations

Re: Convert Milliseconds to Seconds

sunny_talwar — Thu, 02 Aug 2018 11:31:54 GMT

I am confused... when exported to Excel, is this read as number or text? Can you share the exported Excel or it's image with one of the populated cells highlighed

Re: Convert Milliseconds to Seconds

sunny_talwar — Thu, 02 Aug 2018 11:33:24 GMT

Okay, not that makes complete sense.. Let me ask you 2 things

1) Did Chanty's response solve your problem... if it did... no point moving foward

2) If Chanty's response did not solve your problem.... which object are you using here to display your number?

Re: Convert Milliseconds to Seconds

puneetagarwal — Thu, 02 Aug 2018 11:36:52 GMT

See the column AD(TimeInSec)