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

Question & Answer
, , , , , , , ,
1

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

image
image1457×324 36.5 KB

I want to get Denial rate by it.

2

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

3

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.

4

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.

1 Like
5

Hello @Deepshikha1, I think you would want to change these LAC-A calculations to LAC-W, then we can probably make this work. You could try something like this:

ifelse(
distinctCountOver({date_rejected}, [{client_claim_num}], PRE_AGG) > 0 AND
distinctCountOver({date_submitted}, [{client_claim_num}], PRE_AGG) > 0,
1,
0
)

Using the LAC-W aggregations should allow you to check for both of these values. We can change this if necessary, but let me know if that helps!

6

Hello @Deepshikha1, since we have not heard back from you, I will mark my last response as the solution. Please let me know if you have any remaining questions on this topic and I can guide you further. Thank you!