Calculated expression returns wrong number

[#1]

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]

May be you need to force QV to interpret TaskTime:

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

[swuehl]

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]

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: