How to count Max values in dataset where null using Firstsortedvalue - within a KPI/Chart measure

[PJG_56]

I'm looking to determine the following values from a dataset and have tried many approaches and failed.

I can calculate firstsortedvalue of a field using this formula:

=FirstSortedValue(JOB,-ID)

I can then create a count using the following formula:

=if(isnull(date(FirstSortedValue(JOB,-ID))),1,0)

The above both work in straight tables.

What I am trying to achieve is a count of all JOB values where JOB is null and use this in a KPI or Chart expression across the data. 

I believe I need to use the AGGR function in combination with Firstsortedvalue but have tried and failed.

Can anyone offer any suggestions on how I count the Max value of a field against a single ID for null values only?

Or conversely non null values so I can deduct this from the total count.

Thanks in advance

[Kushal_Chawda]

could you share a example of what you are trying to achieve wit sample data?

[PJG_56]

Have attached a simple example in the attached excel. Hope it makes sense.

I want to create a KPI chart object in qliksense which shows the outcomes for the 3 KPI's (in yellow on my attached example) using firstsortedvalue where they are null , either alone or combined nulls in both fields.

Thanks in advance.

[Kushal_Chawda · Accepted solution]

@PJG_56  try below

Count of Records where Max (Next Due Date) is blank

=count(DISTINCT aggr( if(isnull(max([Next Due Date])),ID),ID))

Count of Records where Max (Date A) is blank

=count(DISTINCT aggr( if(isnull(max([Date A])),ID),ID))

Count of Records where Max (Next Due Date) is blank and Max (Date A) is blank

=count(DISTINCT aggr( if(isnull(max([Next Due Date]))  and isnull(max([Date A])),ID),ID))

[PJG_56]

Thank you so much for your support on this item. It looks so simple, but I tried it so many different ways and failed.

Best regards and thanks again

Peter