Set expression - calculation based on state and id

[Micki]

Hi guys, Could please help, 

Could not build set expressions to calculate in the table only last state 'Active' when after was none State 'New' based on week date

Id	State	Set expression	Date
1	Active	0	week1
1	New		week1
3	Active	0	week2
3	New		week2
5	New		week3
5	Active	1	week3

Result should be 1 

Many thanks!

Could you please take a look @sunny_talwar ? 

[sunny_talwar]

How do you determine if Active came first or New? Id and Date (week) seems to be same for both Active and New rows?

[Micki]

Hi, sorry i didn't mention that instead of date i have Timestamp, so I sorted by ID + THEN week number based on Timestamp

[sunny_talwar · Accepted solution]

So, you can try one of the two

Sum(Aggr(
  If(FirstSortedValue(State, -TimeStampField) = 'Active', 1, 0)
, Id))

or

Count(DISTINCT {<Id = {"=FirstSortedValue(State, -TimeStampField) = 'Active'}>} Id)