To Identify Common Clients Across 'Date Submitted' and 'Date Rejected' Charts (Urgent assistance/help required)

I have two charts in AWS QuickSight:

  • One showing claims submitted with information like the submission date, client claim number, and claim ID.
  • The other showing rejected claims with information like the rejection date, client claim number, and claim ID.
  • Both dates are from same dataset only.

I want to know the count of unique clients whose claims were rejected among the submitted claims. In simpler terms, we’re looking for clients who both have submitted claims and claims rejected. can it be done in same chart? Where i can compare also and can see.
In filter i am looking for 4 months period. in that period i want to divide total submitted claim by / how many among them got rejected which are in submitted also.
Goal = Claim by submission date will be my total submitted Claim by rejected date will be total rejected

I want to get Denial rate by it.

Hello @Deepshikha1 ,
to get the distinct count of your rejected claims with respect to open claims of the client I believe that you could try an “IF” Statement using “LAC’S”. You could try writing an IF Statement where you check for the number of distinct dates rejected per client ID but only if the distinct date submitted count per client ID is < 0.

I hope that this will help you.

Best
Nicolas

Hello @Nicolas_H, @DylanM
We want to find out if the claim IDs in column B are also present in column A. To do this, we’ll create a new column where we count how many times each claim ID from column B appears in column A. This will help us identify which claim IDs are common between the two columns.

Hi @Nicolas_H, @DylanM
distinct_count({date_rejected},[{client_claim_num])
distinct_count({date_submitted},[{client_claim_num])
ifelse((distinct_date_submitted_count > 0) && (distinct_date_rejected_count > 0), 1, 0)

While using this formulae getting the below mentioned error:
The level aware calculation (LAC) aggregate functions inside one visual aggregate should always share the same group key.