topic Re: Count only the values with a max date. in QlikView

Count only the values with a max date.

kikerodriguez10 — Mon, 26 Jan 2026 18:19:17 GMT

Hi everyone!

I have a table like this:

ID	Date	Category
1	20170125	1
1	20170312	2
2	20170103	1
2	20170227	1
3	20170130	1
3	20170405	1

I want count only the IDs where the max date of the ID are category 1. If the max date of the ID is different to category 1, don't have to count.

For example if I do a count in this case the result is 2 (Second value of ID 2 and ID 3 because this has category 1 on its highest date.

Thank you for you support!

Regards!

Re: Count only the values with a max date.

vishsaggi — Tue, 17 Jul 2018 22:44:05 GMT

Try this?

Checking:

LOAD *, Floor(Date#(Date, 'YYYYMMDD')) AS DateID INLINE [

ID, Date, Category

1, 20170125, 1

1, 20170312, 2

2, 20170103, 1

2, 20170227, 1

3, 20170130, 1

3, 20170405, 1

];

Left Join(Checking)

LOAD ID,

Max(DateID) AS MaxDateID

Resident Checking

Group By ID

;

LEFT JOIN(Checking)

LOAD ID,

IF(DateID = MaxDateID, 1, 0) AS MaxFlag

Resident Checking

WHERE Category = 1;

Then using straight table add ID as dimension and

Expr: = Count({<MaxFlag = {1} >} ID)

Re: Count only the values with a max date.

kikerodriguez10 — Wed, 18 Jul 2018 17:50:21 GMT

Hi Vishwarath!

Thanks for your help!

This solution its very useful and works but I need that be flexible with filters and this value show in a text object so I think that I need to do with set analysis.

I did a set analysis with aggr function like this:

=sum(aggr(count(DISTINCT {<Category={1}>}ID),ID))

This does not consider the Date, only consider the distinc values with Category 1.

How I can consider in this set analysis the max date value for each ID?

Thanks!

Re: Count only the values with a max date.

sunny_talwar — Wed, 18 Jul 2018 18:15:24 GMT

May be this

Count(DISTINCT {<ID = {"=Max(Date) = Max({<Category = {'1'}>}Date)"}>} ID)

Re: Count only the values with a max date.

kikerodriguez10 — Thu, 19 Jul 2018 16:52:03 GMT

Hi Sunny!

It works fine!

Thank you very much for your help!