Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
try this
Use =Avg(Interval(DelDate-CreatDate,'D'))
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))
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.
What output you need?
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;
Number of days in average...
Thanks...Maybe it will work...let me try 🙂
what is "Record" referring to here?