Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
@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))
could you share a example of what you are trying to achieve wit sample data?
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.
@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))
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