topic Re: how to calculate average between two dates? in QlikView

how to calculate average between two dates?

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

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)

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

Need assistance. Thanks in advance

Re: how to calculate average between two dates?

Chanty4u — Sat, 19 May 2018 06:11:51 GMT

try this

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

Re: how to calculate average between two dates?

mikaelsc — Sat, 19 May 2018 14:37:19 GMT

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))

Re: how to calculate average between two dates?

rubenmarin — Sun, 20 May 2018 08:27:43 GMT

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.

Re: how to calculate average between two dates?

Anil_Babu_Samineni — Thu, 24 May 2018 03:08:54 GMT

What output you need?

Re: how to calculate average between two dates?

qlikviewwizard — Thu, 24 May 2018 03:18:51 GMT

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;

Re: how to calculate average between two dates?

Anonymous — Thu, 24 May 2018 04:14:35 GMT

Number of days in average...

Re: how to calculate average between two dates?

Anonymous — Thu, 24 May 2018 04:16:11 GMT

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

Re: how to calculate average between two dates?

jakobjensen — Fri, 14 Jan 2022 12:33:14 GMT

what is "Record" referring to here?