Announcements
MAINTENANCE ALERT: Dec. 12th starting 9 AM CET. Community will be read-only. GET DETAILS
cancel
Showing results for
Did you mean:
Not applicable

## Expression Help

Am I just trying to do too much in one expression?  If I run the individual pieces they work but when I try to get the overall AVG I get '-'

=AVG(if(REFERRAL_STATUS_ID='100',(floor((sum(floor(num(SERVICE_DATE)-num([Entry Date]))))/count(REFERRAL_ID))),0))

Breaking it down...

Any time the referral status is 100 (Completed), I want to find the average number of days from the start of the referral (Entry date) to the time it was scheduled (Sched Date).  If the status is not 100 then I want to disregard it completely.

I would like to use the expression in a text box to just show the average number of days

1 Solution

Accepted Solutions
Former Employee

You cannot have as Sum() function inside an Avg() function without using Aggr(). So I think you should use

Avg(Aggr(Sum({\$<REFERRAL_STATUS_ID={'100'}>} SERVICE_DATE-ENTRY_DATE) / Count({\$<REFERRAL_STATUS_ID={'100'}>} distinct REFERRAL_ID),REFERRAL_ID))

This, however, assumes that you have interpreted the dates correctly in the script.

HIC

3 Replies
MVP

Hi

Try like this

=AVG(if(REFERRAL_STATUS_ID='100', Sum(Floor(SERVICE_DATE)-Floor(ENTRY_DATE)) /count(REFERRAL_ID),0))

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.
Specialist

Hi,

Try like this:

Script:

FieldName1,

(Floor(SERVICE_DATE)-Floor(ENTRY_DATE)) AS ServiceCount,

....

....

From TableName;

Use the expression like this

=Alt(Sum({\$<REFERRAL_STATUS_ID='100'>}ServiceCount) > 0,

(Sum({\$<REFERRAL_STATUS_ID='100'>}ServiceCount) / Count({\$<REFERRAL_STATUS_ID='100'>}REFERRAL_ID)),0)

regards,

Former Employee

You cannot have as Sum() function inside an Avg() function without using Aggr(). So I think you should use

Avg(Aggr(Sum({\$<REFERRAL_STATUS_ID={'100'}>} SERVICE_DATE-ENTRY_DATE) / Count({\$<REFERRAL_STATUS_ID={'100'}>} distinct REFERRAL_ID),REFERRAL_ID))

This, however, assumes that you have interpreted the dates correctly in the script.

HIC

Community Browser