topic Re: Expression Help in QlikView

Expression Help

Fri, 13 Sep 2013 19:31:07 GMT

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

Re: Expression Help

MayilVahanan — Fri, 13 Sep 2013 19:37:48 GMT

Try like this

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

Re: Expression Help

v_iyyappan — Fri, 13 Sep 2013 19:45:45 GMT

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,

Re: Expression Help

hic — Sat, 14 Sep 2013 11:56:07 GMT

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