average assignment time

[tripatirao]

Hi all,

I want to calculate average assignment time.

I have written the following expression but it is not working for me to calculate average.

=avg(Interval([Creation timeIMS]-[Creation timeDMS],'hh:mm:ss'))

I have attached the sample source file for this.

Please help me to findout.

Regards

Triapti

[swuehl]

Double check that the timestamp values are interpreted as numbers, e.g. by setting the default format:

SET TimestampFormat = 'DD-MM-YYYY hh:mm:ss';

Why don’t my dates work?

Then,

=Interval(Avg([Creation timeIMS]-[Creation timeDMS]),'hh:mm:ss')

edit:

Your records show either one of the creation times, so above calculation will only work if Only([Creation timeIMS]) and Only([Creation timeDMS]) return both values in two expressions for the scope you are evaluating.

But then averaging would be meaningless.

I think you need to transform your data so that the timestamps are accessible single records scope.

[rubenmarin]

Hi tripati, in that excel the dates are in different rows. a subtraction like number-null() or null()-number will return null.

To make it work both dates should be in the same row, maybe with a group by some of the columns helps in grouping the dates in one row, ie:

TimeDifferences:

LOAD [Ticket No],

     Min([Creation timeDMS]) as [Creation timeDMS2],

     Min([Creation timeIMS]) as [Creation timeIMS2]

     Interval([Creation timeIMS]-[Creation timeDMS],'hh:mm:ss') as [Difference time]

Resident yourTable;