Aggregate and count function

[danimelo1]

Hi experts,

From the table above, I want to count the number of active members. I'm trying with this expression but seems it doesn't works in my KPI label:

=aggr(count{<[S]={'3'}>}(max(date([Start Date]))distinct [User Sys ID]),[User Sys ID])

This is my table:

The count should be equal to 2 (User 8 and 9). The tricky part is that if a User Sys ID has S=3 in the last start date, it is an active user.

I think this is a good approach but I can't figure out how to implement it.

Any idea?

[vishsaggi · Accepted solution]

Can you try this?
Actions:
LOAD * Inline [
"User Sys ID","Start Date",S
8,1/24/2014,0
8,1/24/2014,3
9,7/1/2013,3
9,6/12/2016,1
9,12/10/2016,3
13,5/1/2014,3
13,7/1/2015,3
13,11/1/2015,3
13,11/25/2015,3
13,7/1/2016,0
13,7/1/2016,3
90,1/1/2014,3
90,4/10/2017,0
];

LEFT JOIN(Actions)
LOAD [User Sys ID],
Date(Max([Start Date])) AS MaxStDt
Resident Actions
Group By [User Sys ID];

In your chart add [User sys ID] as dimension and expression as
= Sum(If(S = 3 and [Start Date] = MaxStDt,1, 0))