topic Re: average assignment time in QlikView

average assignment time

tripatirao — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: average assignment time

swuehl — Thu, 27 Oct 2016 13:50:06 GMT

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.

Re: average assignment time

rubenmarin — Thu, 27 Oct 2016 13:55:11 GMT

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;