topic Re: Calculated expression returns wrong number in QlikView

Calculated expression returns wrong number

Wed, 08 Jul 2015 13:22:34 GMT

I am trying to match the time taken on a task with the time allowed for that task.

My duration calculation works fine:

= interval(CompletedOn - AssignedOn,'m')

CompletedOn and AssignedOn are both date fields.

However the formula checking the variation of duration against TaskTime (the allowed time) gives a completely wrong figure.

=num(TaskTime - interval(CompletedOn - AssignedOn,'m'),'####.##')

For example duration = 24 minutes

TaskTime = 60 minutes

Variance = 59.98

Can anyone help?

sunny_talwar — Wed, 08 Jul 2015 15:20:22 GMT

May be you need to force QV to interpret TaskTime:

=Num(Interval#(TaskTime, 'm') - Interval(CompletedOn - AssignedOn, 'm'), '####.##')

swuehl — Wed, 08 Jul 2015 15:25:03 GMT

Assuming that TaskTime is given in minutes, and you want to return also a value in minutes, maybe like

=num(TaskTime - num#(interval(CompletedOn - AssignedOn,'m')),'####.##')

Note that interval() only change the format, but not changes the underlying value.

sunny_talwar — Wed, 08 Jul 2015 15:28:29 GMT

Try this script:

Table:

LOAD *,

Interval(Interval#(TaskTime, 'm') - Difference, 'm') as DelayInMinutes,

Num(Interval#(TaskTime, 'm') - Difference, '####.##') as DelayInNum;

LOAD *,

Interval(CompletedOn - AssignedOn, 'm') as Difference Inline [

Task, TaskTime, AssignedOn, CompletedOn

1, 60, 05/01/2015 5:40:22 PM, 05/01/2015 7:22:37 PM

];

Output: