Count only the values with a max date.

[kikerodriguez10]

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!

[qlikviewwizard]

Hi,

I did not understand. Can you explain in detail?

[tresesco]

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)

[pooja_prabhu_n]

Hi,

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

or

Max(date) as Max_date

group by category

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

[JaMajka1]

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

[kikerodriguez10]

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!

[kikerodriguez10]

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!

[kikerodriguez10]

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!

[kikerodriguez10 · Accepted solution]

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.