topic Re: Avg function in App Development

Avg function

NemoAndStitch22 — Sat, 16 Nov 2024 19:38:46 GMT

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

Re: Avg function

Anil_Babu_Samineni — Tue, 19 Nov 2019 10:22:58 GMT

Try this?

Avg(Interval(Survey Date-Enquiry Date, 'DD')) as Days

Re: Avg function

NemoAndStitch22 — Tue, 19 Nov 2019 10:27:26 GMT

I'm getting the same error of 'invalid expression' 😔

Re: Avg function

Anil_Babu_Samineni — Tue, 19 Nov 2019 10:42:47 GMT

What this is returning?

Interval(Survey Date-Enquiry Date, 'DD')

Re: Avg function

NemoAndStitch22 — Tue, 19 Nov 2019 10:45:47 GMT

That bit loads in fine and is giving me the correct data. as per the photo.

Re: Avg function

sunny_talwar — Tue, 19 Nov 2019 12:44:07 GMT

Aggregate functions such as Avg, Sum, Min, Max etc need group by clause... are you using one when you do Avg()?

Re: Avg function

NemoAndStitch22 — Tue, 19 Nov 2019 13:52:05 GMT

Sorry, I don't understand what you mean by a clause..

In my data load I tried doing this:

Resident Enquiries, group by Branch

(All formatted correctly) which it didn't like, is that what you mean?

Re: Avg function

sunny_talwar — Tue, 19 Nov 2019 13:53:46 GMT

That is what I meant, but what all fields and expressions did you have within Load?

Re: Avg function

NemoAndStitch22 — Tue, 19 Nov 2019 13:57:47 GMT

This is what I have tried, and the greyed out attempts didn't work with it like this.

Re: Avg function

sunny_talwar — Tue, 19 Nov 2019 14:01:50 GMT

Group by needs to include all the fields that are listed outside of aggregation... so, Year, Month, MonthYear etc will all need to go into Group By or you need to remove all fields except Brand from your Load...

Re: Avg function

NemoAndStitch22 — Tue, 19 Nov 2019 14:09:23 GMT

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

Re: Avg function

Anil_Babu_Samineni — Wed, 20 Nov 2019 13:39:42 GMT

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

Re: Avg function

NemoAndStitch22 — Wed, 20 Nov 2019 13:43:01 GMT

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