Median Calculation for Distinct Case Closure Days

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

Hello QuickSight Community,

I am facing an issue with calculating the median of “Number of days to close the case” for unique CaseIDs in QuickSight.

Dataset Overview: My dataset consists of CaseIDs, each with associated “Number of days to close the case.”
Some CaseIDs appear multiple times with identical numbers of days due to how the data model is structured.

Here’s a sample of the data:

CaseID Number of days to close the case
C1 2
C1 2
C2 4
C2 4
C3 5
C4 8
C5 6

Goal: I need to calculate the median number of days it takes to close cases, considering each CaseID uniquely.

Approach and Issue: To handle duplicate CaseIDs with the same number of days, I used minOver in the data preparation step to ensure each CaseID is considered only once:

minOver({Number of days to close the case}, [{CaseID}], PRE_AGG)

Despite this, the KPI card in QuickSight shows a median of 4, whereas the correct median calculated manually from the unique dataset (2, 4, 5, 6, 8) is 5.

Thank you in advance for your help!

2

@Purushothaman because the KPI chart does not have GROUP BY field, your calculated field output was calculated against minOver of all the rows (2,2,4,4,5,8,6).

Instead, you can try the following calculated field.

median(sum({Number of days to close the case}, [CaseID])/count({Number of days to close the case}, [CaseID]))

I tested and it works.

image
image996×419 34.1 KB

1 Like
3

@ytakahr Thank you very much for your quick solution. Much Appreciated.