how to calculate average between two dates?

[Anonymous]

Hi,

I have two dates column..need to find average days between these two column..

Data as below:

CreateDate	DeliveryDate
3-Apr-17	20-Apr-17
5-Apr-17	18-Apr-17
5-Apr-17	6-Apr-17
4-Apr-17	15-Apr-17
5-Apr-17	10-Apr-17
5-Apr-17	22-Jun-17
5-Apr-17	8-Apr-17

I use these formula both give same result:

=Round(Avg(DeliveryDate-CreateDate),0.01)

or

=Round(Avg(num(DeliveryDate,'#,##0')-num(CreateDate,'#,##0')),0.01)

Need assistance. Thanks in advance

[Chanty4u]

try this

Use   =Avg(Interval(DelDate-CreatDate,'D'))

[mikaelsc]

you want to calculate the avg per record, don't forget to AGGR (look this up, really, will be useful for your further dev)

avg(aggr(Sum(DeliveryDate-CreateDate),Record))

[rubenmarin]

Hi Sanjida, maybe dates are loaded as a string, not as dates, converting to date to make the calculation can be done as:

=Round(Avg(date#(DeliveryDate, 'D-MMM-YY')-date#(CreateDate, 'D-MMM-YY')),0.01)

Check the MonthNames variable at start of the script, the short name for months has to be the same as the data loaded.

[Anil_Babu_Samineni]

What output you need?

[qlikviewwizard]

Hi,

Please check this.

DataTemp:

LOAD *,num#(Interval(DeliveryDate-CreateDate,'D'),'###') AS NoOfDays;

LOAD date#(CreateDate,'DD-MMM-YY') as CreateDate,date#(DeliveryDate,'DD-MMM-YY') as DeliveryDate INLINE [

CreateDate,DeliveryDate

3-Apr-17,20-Apr-17

5-Apr-17,18-Apr-17

5-Apr-17,6-Apr-17

4-Apr-17,15-Apr-17

5-Apr-17,10-Apr-17

5-Apr-17,22-Jun-17

5-Apr-17,8-Apr-17

];

NoConcatenate

Data:

Load CreateDate,DeliveryDate,sum(NoOfDays)/2 as AverageDays,sum(NoOfDays) as NoOfDays Resident DataTemp

group by CreateDate,DeliveryDate;

drop table DataTemp;

[Anonymous]

Number of days in average...

[Anonymous]

Thanks...Maybe it will work...let me try 🙂

[jakobjensen]

what is "Record" referring to here?