Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

ruma_barman
Contributor

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

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

May be try this

Dimension

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

Expression

=Count(DISTINCT contract_id)
1 Reply

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

May be try this

Dimension

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

Expression

=Count(DISTINCT contract_id)