topic Interval and Number comparison in App Development

Interval and Number comparison

Wed, 02 Mar 2016 13:56:42 GMT

Hi all,

So I have a time interval declared at load which shows the difference between the entry and exit time in minutes:

Interval([ExitedTime]-[EnteredTime],'mm') As [DurationActual]

And a [Duration] field which has set amounts of 15, 30, 45, 60, 75 and 90 representing minutes allotted to a client.

I wanted to compare the two to show whether a client stayed within their allotted time allowance and use a background colour expression to indicate if they were over or under:

if([DurationActual]>[Duration],red(),green())

This always shows as green though even when DurationActual is higher than the Duration. I'm thinking it is an issue with the formatting when comparing the two. Can anyone advise on how to convert either of them into comparable formats?

Many thanks.

Re: Interval and Number comparison

Wed, 02 Mar 2016 15:23:20 GMT

you could try putting NUM around both (either in the expression or the load script):

NUM( Interval([ExitedTime]-[EnteredTime],'mm'))

and

NUM([Duration])

that is assuming you have already linked [Duration] to the tables some how (so there is only one [Duration] to compare against)

Re: Interval and Number comparison

sunny_talwar — Wed, 02 Mar 2016 15:27:06 GMT

Would you be able to share a sample?

Re: Interval and Number comparison

Wed, 02 Mar 2016 15:32:22 GMT

Hi Sunny,

See below.

Since Qliksense doesn't actually let me change the colour of a normal column you'll see I've got duration as a calculation here as well but the if statement in the colour change is the issue. DurationActual shows the exact same values as Duration(Actual) column.

Re: Interval and Number comparison

Wed, 02 Mar 2016 15:47:23 GMT

INTERVAL returns a fraction to represent time - it just makes it look like a minute representation.

i.e. if you put NUM around the interval, you will see a completely different number to without it. You could try multiplying [DurationActual] * 24 * 60

although you run the risk of having fractional variance in the value it returns.

Re: Interval and Number comparison

Wed, 02 Mar 2016 15:51:16 GMT

This fixed it! Thanks Andrew.

if([DurationActual]*24*60>[Duration],red(),green())