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.

1 Like
2

Hello @Deepshikha1, it seems like you will need to utilize LAC-W calculations to get the value you are looking for. Are there ever scenarios where a claim is rejected but not submitted? If not, we could do this in a few calculated fields. Since you are filtering the data down to 4 months, we want to make sure the clients being counted are based on one of the 2 date fields. To accomplish this we could nest the aggregations so it will relate to the partition field.

sumOver(distinctCountOver({client_id}, [{date_submitted}], PRE_AGG), [], PRE_AGG)/sumOver(distinctCountOver({client_id}, [{date_rejected}], PRE_AGG), [], PRE_AGG)

The distinctCountOver is partitioned by date_submitted to ensure it grabs client ids linked to that field, then it will sum the total within your date filter. I’ll mark this as the solution, but let me know if you have any remaining questions!

4

Hello @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.

5

Hi @DylanM
It didn’t work for me, moreover I need the value in percentage.
We have different filters for both of the charts which are as follows:
For left chart I have created a filter for Provider ID and for right chart filters created are Provider ID and Source Name.
So to rectify my issue I don’t want the value directly but the above mentioned filter’s condition must be fulfilled to get the value in %.

1 Like
6

Hello @Deepshikha1, I’ll try to tackle some of these questions, but I may need some more information from you. It would also be beneficial if you could build a demo of this analysis in QuickSight Arena so I can test out some solutions for your use case.

If you want to make sure the Provider ID matches between the 2 parameter values before running the calculations, you can add them in the check of an ifelse statement to return the value. It would look like this:
ifelse(${Provider ID1} = ${Provider ID2}, {calculated field}, NULL)
Then you would only receive values when those 2 things matched.

Now, you want a percent value that would determine the percent of the claims submitted that were rejected, am I correct? Let me try and rework the above calculation to get that value.

sumOver(distinctCountOver({claim_id}, [{date_rejected}], PRE_AGG), [], PRE_AGG)/sumOver(distinctCountOver({claim_id}, [{date_submitted}], PRE_AGG), [], PRE_AGG)

The numerator value should get the distinct count of claim_ids per date_rejected and then sum based on the number of rows you are visualizing. The date_rejected partition should ensure they were rejected.

Alternatively, we could use an ifelse statement nested inside of the distinctCountOver function to ensure we only get claims that were rejected.

distinctCountOver(ifelse(isNotNull({date_rejected}), {claim_id}, NULL), [], PRE_AGG)

Then you to get your total claims value, you would just replace the ifelse statement with your claims_id field. Then you would be able to compare the 2, get the percent values, and so on. Let me know if this helps!