Avg function

[NemoAndStitch22]

Hiya,

I'm creating a script and want to do the average days between enquiry and survey.

I've loaded in my data and created the metric of (Survey Date-Enquiry Date) as Days between survey and Enquiry

I've then noconcatenated this and loaded it again as a proper field.

Then tried to do

Avg(Days between Enquiry and Survey) as Avg Days.

When I load this in it kicks up a fuss. 

Does anyone know what I'm doing wrong?

Thanks

[NemoAndStitch22]

It doesn't like that...The previously shared script before adding the group by was fine, and now it's not...

[Anil_Babu_Samineni]

Try to share full script of yours that shows Avg and Group By if u r trying in script?

[NemoAndStitch22]

Here  you go:

Enquiries:
LOAD

Date#(subfield(FileBaseName(),' ',7),'YYYY') as Year,
Date#(subfield(FileBaseName(),' ',6),'MMM') as Month,
Date#(subfield(FileBaseName(),' ',6)&' '&subfield(FileBaseName(),' ',7),'MMM YYYY') as MonthYear,

No.,
Description,
"Description 2",
Status,
"Last Amended",
"Global Dimension 1 Code" as "Branch",
"Enquiry Owner",
Surveyor,
"Enquiry Date",
"Survey Appointment",
"Survey Enquiry Status",
"Loss Reason Comment",
"Call Code",
"Symptom 1",
"Symptom 2",
"Symptom 3",
"Symptom 4",
("Survey Appointment"-"Enquiry Date") as "Days between Enquiry and Survey"

FROM [lib://Enquiry Extract/Enquiry Extract*.xlsx]
(ooxml, embedded labels, table is [Book 1]) where not "Survey Appointment" like '';

NoConcatenate
Enquiries2:
LOAD

Year,
Month,
MonthYear,
No.,
Description,
"Description 2",
Status,
"Last Amended",
"Branch",
"Enquiry Owner",
Surveyor,
"Enquiry Date",
"Survey Appointment",
"Survey Enquiry Status",
"Loss Reason Comment",
"Call Code",
"Symptom 1",
"Symptom 2",
"Symptom 3",
"Symptom 4",
"Days between Enquiry and Survey",
Interval("Survey Appointment"-"Enquiry Date", 'DD') as Days

//Avg("Days between Enquiry and Survey") as "Avg Days"
//if("Days between Enquiry and Survey"='>0',(Avg("Days between Enquiry and Survey")),'0') as "Average Days"
// avg("Days between Enquiry and Survey") as days

Resident Enquiries Group By Year,
Month,
MonthYear,
No.,
Description,
"Description 2",
Status,
"Last Amended",
"Branch",
"Enquiry Owner",
Surveyor,
"Enquiry Date",
"Survey Appointment",
"Survey Enquiry Status",
"Loss Reason Comment",
"Call Code",
"Symptom 1",
"Symptom 2",
"Symptom 3",
"Symptom 4",
"Days between Enquiry and Survey" ;

drop table Enquiries