topic Re: How to count Max values in dataset where null using Firstsortedvalue - within a KPI/Chart measure in App Development

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

PJG_56 — Thu, 16 Sep 2021 09:51:01 GMT

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

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

Kushal_Chawda — Thu, 16 Sep 2021 10:12:23 GMT

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

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

PJG_56 — Thu, 16 Sep 2021 11:06:15 GMT

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.

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

Kushal_Chawda — Thu, 16 Sep 2021 11:32:40 GMT

@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))

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

PJG_56 — Thu, 16 Sep 2021 11:54:21 GMT

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