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
sunny_talwar

May be this

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


Capture.PNG

View solution in original post

4 Replies
vishsaggi
Champion III
Champion III

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)

kikerodriguez10
Contributor III
Contributor III
Author

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!

sunny_talwar

May be this

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


Capture.PNG

kikerodriguez10
Contributor III
Contributor III
Author

Hi Sunny!

It works fine!

Thank you very much for your help!