Counting a field only if a certain condition apply

[QlikSensor]

Hi, I have following table:

Extraction Date, ID

Now I want to count only those IDs which have the newest Extraction Date. Note that "Extraction Date" is of type date.

My first trial was, to mark the newest Extraction Date in the data load script:

IF(peek([Extraction Date])=[Extraction Date],1,0) as "Latest Extraction Date"

But this marks data randomly with a 1.

Next I tried to solve it like this directly in the diagram:

count(if([Extraction Date]=max([Extraction Date]),[ID]))

But the diagram cant grab data it says.

Can you please help me on that?

[Pierrick · Accepted solution]

Hello @QlikSensor,

maybe with this directly in your diagram : 

Count({<[Extraction Date]={"$(=max([Extraction Date]))"}>}[ID])

 In your formulas, try using set analysis instead of "if" (Set analysis documentation)

[maxgro]

If you want to flag the records with max extraction date in the script.....

Table1:

LOAD * INLINE [

ID, Extraction Date

0, 02/04/2024

1, 01/01/2020

2, 02/02/2020

3, 10/01/2024

4, 02/04/2023

5, 02/02/2022

6, 02/02/2021

7, 02/04/2024

8, 02/04/2024

9, 02/04/2024

];

LEFT JOIN (Table1) LOAD

    DATE(MAX([Extraction Date])) AS [Extraction Date],

    1 AS LatestExtractionDate

RESIDENT Table1;

.....and in the chart Count({$<LatestExtractionDate={1}>} ID)

[QlikSensor]

Perfect. Thanks for that. If I need a further condition, can I couple it with a simple and? E. g.:

Count({<[Extraction Date]={"$(=max([Extraction Date]))"} and version="v19">}[ID])

[GOKULAKANNAN]

Hi,

You have to do like below.

Count({<[Extraction Date]={"$(=max([Extraction Date]))"}, version={'v19'}>}[ID])

[Pierrick]

Hello @QlikSensor,

Yes, you can combine several conditions, but not with an AND, but with a comma and your 'v19' need to be in {}, as say @GOKULAKANNAN.

Feel free to have a look at the set analysis documentation. It may seem complicated at first, but there's a lot you can do with it.