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

Count only the values with a max date.

kikerodriguez10 — Wed, 25 Nov 2020 16:16:04 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.

qlikviewwizard — Wed, 18 Jul 2018 03:09:22 GMT

Hi,

I did not understand. Can you explain in detail?

Re: Count only the values with a max date.

tresB — Wed, 18 Jul 2018 04:43:05 GMT

It seems that highest date is vague here. You just need to count IDs except 1. The same could be done using:

Count(distinct {<ID-={1}>}ID)

Re: Count only the values with a max date.

pooja_prabhu_n — Wed, 18 Jul 2018 05:38:11 GMT

Hi,

=Count({<Date={'$(=max(Date))'}>} ID)

Max(date) as Max_date

group by category

=Count(if(Date=Max_date,ID))

Re: Count only the values with a max date.

JaMajka1 — Wed, 18 Jul 2018 05:47:48 GMT

Hi Enrique,

if you do not need this to be flexible with filters in frontend you can solve it in the script by adding the flag @relevant to each row of your table. If you order Date within each ID from max to min then maximum value of Date will be the first -> previous row will have different ID. After that you can count or do whatever you want with only Dates flagged as @relevant.

[Tmp]:

Load * Inline [ID,Date,Category

1,20170125,1

1,20170312,2

2,20170103,1

2,20170227,1

3,20170130,1

3,20170405,1

];

[Tmp_2]:

load

ID,

Date,

Category,

if(previous(ID)<>ID and Category=1,1,0) as @relevant

resident Tmp

order by

ID asc,Date desc;

drop table Tmp;

Hope it will help ,

Maria

Re: Count only the values with a max date.

kikerodriguez10 — Wed, 18 Jul 2018 17:09:18 GMT

Hi Arjun,

Thanks for your help!

I hope this explanation is a little simpler:

I need to count the maximum dates of each ID and that they are category 1. Each ID can have different records with different dates but I am interested in considering in the count the value of the maximum date and that is category 1. This result must be dynamic so that it can work with the Year or Month selections that the user gets to make.

Thanks!

Re: Count only the values with a max date.

kikerodriguez10 — Wed, 18 Jul 2018 17:19:44 GMT

Hi Tresesco!

Sorry if I do not clearly explain this problem but I need to count the maximum dates of each ID and that they are category 1. Each ID can have different records with different dates but I am interested in considering in the count the value of the maximum date and that is category 1 and is very important that the result must be dynamic so that it can work with the Year or Month selections that the user gets to make.

Thanks for your help!

Re: Count only the values with a max date.

kikerodriguez10 — Wed, 18 Jul 2018 17:33:17 GMT

Hi Maria!

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.

I did a set analysis with aggr function like this:

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

This set analysis does nott consider the Date son 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.

kikerodriguez10 — Fri, 20 Jul 2018 16:43:58 GMT

Hi everyone!

I found the solution.

I used this solution and it worked for me:

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

I hope you find it useful.

Thanks you for your help!

Enrique.