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!!
you can create calc field using distinct_countIf() like the below.
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.
thankyou for the response, can i know PRE_FILTER is a must or just an optional?
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.
Thankyou Wakana! This is very helpful infomartion for newbie like me.