Qlik Community

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
vipul_narang
Contributor II
Contributor II

Converting seconds to 'hh:mm:ss' format is not working properly

Hello,

I am using an extension object "climber KPI version 1.5.0"

By using that, i am showing a metric versus a benchmark. For example, Time on site is in seconds and by using the following formula , i for the metric and benchmark i get the output as the image attached (TimeOnSite)

=(Time(($(vBenchmarkTimeOnSite))/(3600*24),'hh:mm:ss'))

Which is how it should work.

But when i use the same for a different metric with similar calculation and numbers, the result comes out to be less than 1 minute (which is correct) but then the difference it is showing with relation to the benchmark is different (check image - TimeOnPage)

Can anybody please help me resolve this issue?

Thanks!

VN

9 Replies
MK9885
Master II
Master II

I think first load the dates as it is in Qlik and see how the date format is being loaded in front end

Then you can use formatting to convert.

Whats your original format in Qlik front end?

jonathandienst
Partner
Partner

Use "Interval()" instead of "Time()" for the difference values in the second KPI.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
vipul_narang
Contributor II
Contributor II
Author

Thanks Jonathan. But i tried Interval() instead of Time() but its still the same.

vipul_narang
Contributor II
Contributor II
Author

I am not working with dates.

I am working with a metric in seconds(session duration) which is in the numerator and the Sessions in the denominator which is just a count of sessions across which the session duration is being measured.

timpoismans
Specialist
Specialist

Your issue at hand is that he doesn't look at your seconds as actual seconds.

When calculating the difference, you're actually comparing a point in time - another point in time.

So when you have "00:00:51", it's seen as 51 seconds past midnight.

And when you have "00:02:22", it's seen as 2 minutes and 22 seconds past midnight.

Thus, when you calculate the difference, you go back in time from [TimeOnSite], 2minutes and 22 seconds to be exact, and you get the result "23:58:29" on the clock.

But what would you desired output be? A negative value in seconds?

vipul_narang
Contributor II
Contributor II
Author

Yes, the desired output, which is expected with this KPI is the difference between the benchmark (i.e. 00:02:22) and the current value of the metric (i.e. 00:00:51) i.e. 00:01:31 or -00:01:31 or 91  (Something like this would do)

swuehl
MVP
MVP

If I understand correctly, your calculation returns something negative. If you want to show a negative time span (or in general a time span), you need to use Interval() instead of Time(), just as Jonathan said.

Can you double check using a different chart object that Interval() is not working as expected? I don't know your extension object, so just make sure that no format option included with the extension object is messing up with the expression format.

swuehl
MVP
MVP

Have you tried using the absolute value of the difference using fabs()?

vipul_narang
Contributor II
Contributor II
Author

I think that is limitation with this extension.

https://github.com/ClimberAB/ClimberKPI

It is not allowing me to work on the difference it is showing under target mode or maybe i am doing something wrong to calculate it. The numbers by themselves look alright its just the difference that is not working correctly.