Aggregated TAT % in KPI Visual Always Showing 0% Despite Correct Row-Level Logic

Question & Answer
, , ,
1

I am working on a calculated field in Amazon QuickSight to compute TAT % using the following logic:

  • If Last Correction Received Date is null, return 100
  • If Last Correction Submitted Date is null, return 0
  • If both dates are present, calculate the percentage of jobs where the difference between Received and Submitted is less than or equal to 1 day, divided by the total number of jobs in the selected date range

This logic works correctly at the row level. However, when I use the same calculated field in a KPI or gauge visual, the aggregated value always shows 0, even when the row-level values are correct (100 or 0).

Parameters Used:

  • ${StartDate} = 01 Jan 2025
  • ${EndDate} = 22 Jul 2025

Caculated Field:

TAT % :
“ifelse((isNull(max({Last Correction Received Date}))),100,
(isNull(max({Last Correction Submitted Date}))),0,
(min({Last Correction Received Date})>=now() AND (min({Last Correction Submitted Date})<=now())),100,
(coalesce((distinct_countIf({Job_id},
({Last Correction Received Date} >= ${StartDate}
AND {Last Correction Received Date} <= {End Date Added}
AND dateDiff({Last Correction Received Date},{Last Correction Submitted Date},“DD”)<=1)
)*100)
/
(distinct_countIf({Job_id},
({Last Correction Received Date} >= ${StartDate}
AND {Last Correction Received Date} <= {End Date Added})
)
),100)))”

Result:

image
image1002×522 39.4 KB

What I Have Tried:

  • Verified that the calculated field returns correct values in a table visual
  • Confirmed that the field names and parameters are correctly defined
  • Attempted to use coalesce and isNull to handle nulls and fallback values
  • Tested with and without filters

Issue:

Despite all of the above, the KPI visual continues to show 0 as the aggregated value. This happens even when all rows return 100 at the row level due to null Last Correction Received Date.

Screenshots:

  • Table visual showing correct row-level values
  • KPI visual showing 0
  • Formula editor with the current logic

(refer attached screenshots here)

Request:

I would appreciate any guidance on:

  • Why the KPI visual is not reflecting the correct aggregated value
  • Whether there is a better way to structure this logic to work reliably in aggregated visuals
  • Any known limitations or workarounds for this type of calculation in QuickSight

Thank you in advance for your support.

2

Hi @Vaibhav.narwade,

There seems to be a missing closing bracket somewhere in your calculated field. It’s probably a typo in your question if QuickSight isn’t giving you any error. Can you check the syntax? Adding the missing bracket would help understand what your calculated field is doing.

3

Hi @David_Wong,

Please find below the correctly formatted calculated field. The previous version was not formatted appropriately. I can use this calculated field successfully; however, there seems to be an issue with the aggregation value. Thank you for your attention to this matter.

round(
  ifelse(
    isNull(max({Last Correction Received Date})), 100,
    isNull(max({Last Correction Submitted Date})), 0,
    max({Last Correction Received Date}) >= now() AND max({Last Correction Submitted Date}) <= now(), 100,
    
    (
      coalesce(
        (
          distinct_countIf(
            {Job_id},
            {Last Correction Received Date} >= ${StartDate} AND
            {Last Correction Received Date} <= ${EndDate} AND
            dateDiff({Last Correction Received Date}, {Last Correction Submitted Date}, "DD") <= 1
          ) * 100
        ) /
        distinct_countIf(
          {Job_id},
          {Last Correction Received Date} >= ${StartDate} AND
          {Last Correction Received Date} <= ${EndDate}
        ),
        100
      )
    )
  ),
  1
)

Best,
Vaibhav.