Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

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

Re: Interval and Number comparison

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.

5 Replies
Not applicable

Re: Interval and Number comparison

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)

MVP
MVP

Re: Interval and Number comparison

Would you be able to share a sample?

Not applicable

Re: Interval and Number comparison

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

Re: Interval and Number comparison

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

Re: Interval and Number comparison

This fixed it! Thanks Andrew.

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