Duration format conversion depending on value

[ccastrillo]

Hi,

I am trying to calculate the average duration of several incidents using aggr function. My expression works, showing days or HH:MM:SS depending on format selection (Duration or automatic). But what I want is automatic switch depeding on duration value. If duration is above 24h, I wish to show" DD days, hh hours, mm minutes", but if duration is less than a day, the format should be "hh hours: mm minutes". 

The expression I am ussing is 

avg(aggr(interval(date(Max(AuditDate))-date(Min(AuditDate)),'d hh:mm'),IncidentID))

Any ideas?

Many thnaks in advance,

César

[StarinieriG · Accepted solution]

Hi,

try with 

avg(aggr(interval(date(Max(AuditDate))-date(Min(AuditDate)),

If(date(Max(AuditDate))-date(Min(AuditDate))>=1,'d hh:mm:ss','hh:mm:ss')

),IncidentID))

[ccastrillo]

Thanks StarinieriG, it works!