want to display time in days, hours, minutes

[#1]

I have two tables, one with user data and one with transaction data joined by userid. I want to show average time between transactions per user and group by username. my date column is created_at and in mysql I have extracted this column in seconds and then used an expression in Qlikview to display this column in the format "h m s".

What I want is that if time between deposits is less than 1 minute, it should display time in seconds,

if the time between deposits is less than 60miutes, it should display in minutes,

If the time between deposits is more than 60minutes, it should display time in hours

and if the time between deposits is more than 24hours, it should display in days.

Thanks in advance for any help!

[peschu123]

Hi Zainab,

I don't know if I got it right, but pls take a look at attached qvw.

You have to play around with rounding for your reqirements.

Hope it helps and have nice day.

Best Regards,

Peter

EDIT: Ooops, I think I missed the point, that this is not the raw data....But perhaps its helpful for somebody.

Anyway, could you post an example file?

[#3]

my time format in qlikview is 'hh:mm:ss', it is in an expression in a straight table that I have converted time into 'h m s'

in my script the time is in seconds, can you tell me how I can convert it?

[#4]

please see attached sample file

[peschu123 · Accepted solution]

I just changed my previous file...

Pls take a look.

Or do you need the formatting like h m s ?

In this case we can use my IF() and put your expression inside it with the necessary parts for h m s. If I understand you right, you want to format if there is no hour just to show 10m 3s and <1 minute only 3s ?

Use of Dual field is not necessary, but its helpful if you need to sort your values with this field.

[#6]

thank you, this was exactly what I was looking for

[balajikannan2013]

Hi,

I cannot download the file in my laptop, could you please share the formula instead and it will be very helpful. I need to implement the same requirement.