Error: "Table calculation attribute reference(s) are missing in field wells" when trying to apply calculated field values as filter to KPI visual

Q&A
, ,
13

Hi @Xclipse

I came across the post : New to QS - Aggregating inside Calculated Fields for KPI - Question & Answer - Amazon Quick Sight Community and have updated my calculated fields based on David’s advice, as shown below:

new_ContextStatus = ifelse(
// Approaching Risk Window
  sumOver(ifelse(Status = 'Approaching Risk Window' and Type = 'AllSatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG) > 0, 'Approaching Risk Window',   
  sumOver(ifelse(Status = 'Compliant' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG) = 0
  AND 
  sumOver(ifelse(Status = 'Approaching Risk Window' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG) > 0, 'Approaching Risk Window',

// At Risk
  sumOver(ifelse(Status = 'At Risk' and Type = 'AllSatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG) > 0,  'At Risk', 
  sumOver(ifelse(Status = 'Compliant' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG) <
  sumOver(ifelse(Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG)
  and 
  sumOver(ifelse(Status = 'At Risk' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG) > 0,  'At Risk',

//Non-Compliant
  sumOver(ifelse(Status = 'Non-Compliant' and Type = 'AllSatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG) > 0,  'Non-Compliant', 
  sumOver(ifelse(Status = 'Compliant' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG) = 0
  and 
  sumOver(ifelse(Status = 'Non-Compliant'  and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG) > 0,  'Non-Compliant',

  //Compliant
  sumOver(ifelse(Status = 'Compliant' and Type = 'AllSatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG) = 
  sumOver(ifelse(Type = 'AllSatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG),  'Compliant', 
  sumOver(ifelse(Status = 'Compliant' and Type = 'AnySatisfied', 1, 0), [RegistrantId, ContextName], PRE_AGG) > 0,  'Compliant',

  'Non-Compliant'
)

and

new_OverallStatus = ifelse(
  sum(ifelse({new_ContextStatus} = 'Non-Compliant', 1, 0), [RegistrantId]) > 0, 'Non-Compliant',
  sum(ifelse({new_ContextStatus} = 'At Risk', 1, 0), [RegistrantId]) > 0, 'At Risk',
  sum(ifelse({new_ContextStatus} = 'Approaching Risk Window', 1, 0), [RegistrantId]) > 0, 'Approaching Risk Window',
  sum(ifelse({new_ContextStatus} = 'Compliant', 1, 0), [RegistrantId]) > 0, 'Compliant',
  'Non-Compliant'
)

I’m now able to apply filters in the KPI :smiley:

However, I’m facing one remaining issue: I’m unable to use the new_OverallStatus field in charts like Pie, Donut, Bar, etc.
I’m receiving the following error:

The Level Aware Calculation (LAC) aggregate expression doesn’t work with other category calculation at this moment. Please remove the LAC aggreagtion expression from metrics or visual dimensions, or turn off the default other category calculation in visual options

Would you have any suggestions on how to resolve this?

Tagging @David_Wong huge thanks again for your insights!

1 Like