Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Uploads getting stuck in the virus scanner. We are investigating.
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

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

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_idStateTimestamp
26667APPROVED30-07-2019 09:56
26667APPROVED09-06-2019 09:57
27931APPROVED11-06-2019 13:00
27931AUTO-APPROVED04-06-2019 17:27
27842APPROVED31-06-2019  13:38
27842AUTO-APPROVED31-06-2019  10:03
27935APPROVED04-06-2019 17:27
27935AUTO-APPROVED06-06-2019 17:04

Output:

APPROVED3
AUTO-APPROVED1

 

Thanks and Regards,

Ruma

1 Solution

Accepted Solutions
Highlighted

May be try this

Dimension

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

Expression

=Count(DISTINCT contract_id)

View solution in original post

1 Reply
Highlighted

May be try this

Dimension

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

Expression

=Count(DISTINCT contract_id)

View solution in original post