Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
kikerodriguez10
Contributor III
Contributor III

Count only the values with a max date.

Hi everyone!

I have a table like this:

IDDateCategory

1

20170125

1
1201703122
2201701031
2201702271
3201701301
3201704051

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!

1 Solution

Accepted Solutions
kikerodriguez10
Contributor III
Contributor III
Author

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.

View solution in original post

8 Replies
qlikviewwizard
Master II
Master II

Hi,

I did not understand. Can you explain in detail?

tresesco
MVP
MVP

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
Creator III
Creator III

Hi,

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

or

Max(date) as Max_date

group by category


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

JaMajka1
Partner Ambassador
Partner Ambassador

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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
Contributor III
Contributor III
Author

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.