Expression Help

[#1]

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

[MayilVahanan]

Hi

Try like this

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

[v_iyyappan]

Hi,

Try like this:

Script:

Load

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,

[hic · Accepted solution]

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