Count on the basis of maximum timestamp or create a flag for it

[ruma_barman]

Hi All,

I have a requirement to find the count of IDs of different contract state according to the maximum timestamp.

Eg: If month June 2019 is selected and  for a particular contract both the states Approved and Auto-aprroved exist we have to consider the state having highest timestamp+ if the same contract  has contract state for two different month we have to consider for the lowest month 

Input:

contract_id	State	Timestamp
26667	APPROVED	30-07-2019 09:56
26667	APPROVED	09-06-2019 09:57
27931	APPROVED	11-06-2019 13:00
27931	AUTO-APPROVED	04-06-2019 17:27
27842	APPROVED	31-06-2019  13:38
27842	AUTO-APPROVED	31-06-2019  10:03
27935	APPROVED	04-06-2019 17:27
27935	AUTO-APPROVED	06-06-2019 17:04

Output:

APPROVED	3
AUTO-APPROVED	1

Thanks and Regards,

Ruma

[sunny_talwar · Accepted solution]

May be try this

Dimension

=Aggr(FirstSortedValue(State, -Timestamp), contract_id)

Expression

=Count(DISTINCT contract_id)