Chart expression using max, if, count and distinct

robert99 — Sun, 04 Mar 2012 16:31:21 GMT

Im trying to get an expression to work without using script

This chart expression correctly counts the number of call numbers where the job (call) has been completed on time

COUNT (DISTINCT (IF(DATEIN>DATEFSR,CALLNO)))

This chart expression also works

max({1}TOTAL <CALLNO> DATEFSR)

It calculates the maximum date for each call number

But when I combine the two as follows:

COUNT (DISTINCT (IF(

max({1}TOTAL <CALLNO> DATEFSR)

< DATEIN,CALLNO)))

It doesn't calcualte the correct totals. It just gives a null value or zero

I can calculate the max date in script and then it works fine but would prefer to do this all in a table chart expression if possible

Thanks for any help

jagan — Mon, 05 Mar 2012 06:05:48 GMT

Hi,

Try this using AGGR()

=COUNT (DISTINCT Aggr(IF(Max(DATEFSR) < DATEIN,CALLNO), CALLNO))

Regards,

Jagan.

robert99 — Mon, 05 Mar 2012 21:44:20 GMT

jagan

Thank you.

It does exactly what I want.

topic Chart expression using max, if, count and distinct in QlikView