Count Distinct with latest event

Question & Answer
,
1

Let say i have a table;

claim_id, created _at
1, 2022-01-31 03:00:00
2, 2022-01-31 02:00:00
1, 2022-02-15 08:00:00
3, 2022-01-01 01:00:00
2, 2022-02-01 13:00:00
4, 2022-01-01 08:00:00

I want to count distinct using claim_id field, but the condition is i needto count the latest claim_id date. So my output will be total_claim_id = 4. Can someone help me?

Thank you in advance!!

2

you can create calc field using distinct_countIf() like the below.

distinct_countIf({claim_id}, {created_at}=maxOver({created_at},[{claim_id}],PRE_FILTER))

if you would like to get total_claim_id for the distinct count, you can use sumOver() for the above calc field.

hope this helps.

kind regards,
Wakana

3

thankyou for the response, can i know PRE_FILTER is a must or just an optional?

4

yes using LAC is requries as it requires to aggregate in advance. PRE_FILTER or PRE_AGG is option to use with the maxOver().

for LAC, please refer to the blog : Create advanced insights using level-aware calculations in Amazon QuickSight | AWS Business Intelligence Blog for the details.

kind regards,
Wakana

1 Like
5

Thankyou Wakana! This is very helpful infomartion for newbie like me.

2 Likes