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!

[vishsaggi]

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]

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 · Accepted solution]

May be this

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

[kikerodriguez10]

Hi Sunny!

It works fine!

Thank you very much for your help!