Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
robert99
Specialist III
Specialist III

Chart expression using max, if, count and distinct

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

1 Solution

Accepted Solutions
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try this using AGGR()

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

Regards,

Jagan.

View solution in original post

2 Replies
jagan
Luminary Alumni
Luminary Alumni

Hi,

Try this using AGGR()

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

Regards,

Jagan.

robert99
Specialist III
Specialist III
Author

jagan

Thank you.

It does exactly what I want.