Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Interval and Number comparison

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.

1 Solution

Accepted Solutions
Not applicable
Author

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.

View solution in original post

5 Replies
Not applicable
Author

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)

sunny_talwar

Would you be able to share a sample?

Not applicable
Author

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.

CaptureTime.PNG

Not applicable
Author

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.

Not applicable
Author

This fixed it! Thanks Andrew.

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