Calculated Field to convert text/numeric values to standardized currency format

Q&A
, ,
1

With the help of Atlas AI tool I was abl to generate a formula that converts text to currency values. I am concerned that such a lengthy formula might slow down QuickSight if this formula is applied to 50+ columns in a single dashboard. Is there a better way of handling this?

// Atlas refactoring
// QuickSight Currency Formatter - Optimized v2026.01.29
// Converts text/numeric values to standardized currency format

ifelse(
  // ═══════════════════════════════════════════════
  // NULL & TEXT HANDLING
  // ═══════════════════════════════════════════════
  
  // Return null as-is
  isNull({ats-gen 4.0-construction}), {ats-gen 4.0-construction},
  
  // Filter text strings (contains vowels = status text like "Not Calculate", "Review")
  contains(toLower({ats-gen 4.0-construction}), 'a') OR
  contains(toLower({ats-gen 4.0-construction}), 'e') OR
  contains(toLower({ats-gen 4.0-construction}), 'i') OR
  contains(toLower({ats-gen 4.0-construction}), 'o') OR
  contains(toLower({ats-gen 4.0-construction}), 'u'), 
  {ats-gen 4.0-construction},
  
  // ═══════════════════════════════════════════════
  // INTEGERS (No decimals)
  // ═══════════════════════════════════════════════
  
  // Simple integer: 1-999 → "$ 123.00"
  locate({ats-gen 4.0-construction}, '-') = 0 AND
  locate({ats-gen 4.0-construction}, ',') = 0 AND
  locate({ats-gen 4.0-construction}, '.') = 0 AND
  strlen({ats-gen 4.0-construction}) <= 3,
  concat('$ ', {ats-gen 4.0-construction}, '.00'),
  
  // Negative integer: -1 to -999 → "-$ 123.00"
  locate({ats-gen 4.0-construction}, '-') = 1 AND
  locate({ats-gen 4.0-construction}, ',') = 0 AND
  locate({ats-gen 4.0-construction}, '.') = 0 AND
  strlen({ats-gen 4.0-construction}) <= 4,
  concat('-$', substring({ats-gen 4.0-construction}, 2, strlen({ats-gen 4.0-construction}) - 1), '.00'),
  
  // Large integer: 1000+ → "$ 1,234.00"
  locate({ats-gen 4.0-construction}, '-') = 0 AND
  locate({ats-gen 4.0-construction}, '.') = 0 AND
  locate({ats-gen 4.0-construction}, ',') = 0 AND
  strlen({ats-gen 4.0-construction}) > 3,
  concat('$ ', 
    substring({ats-gen 4.0-construction}, 1, strlen({ats-gen 4.0-construction}) - 3),
    ',',
    substring({ats-gen 4.0-construction}, strlen({ats-gen 4.0-construction}) - 2, 3),
    '.00'),
  
  // Negative large integer: -1000+ → "-$ 1,234.00"
  locate({ats-gen 4.0-construction}, '-') = 1 AND
  locate({ats-gen 4.0-construction}, '.') = 0 AND
  locate({ats-gen 4.0-construction}, ',') = 0 AND
  strlen({ats-gen 4.0-construction}) > 4,
  concat('-$', 
    substring({ats-gen 4.0-construction}, 2, strlen({ats-gen 4.0-construction}) - 4),
    ',',
    substring({ats-gen 4.0-construction}, strlen({ats-gen 4.0-construction}) - 2, 3),
    '.00'),
  
  // Integer with commas: 1,234 → "$ 1,234.00"
  locate({ats-gen 4.0-construction}, '-') = 0 AND
  locate({ats-gen 4.0-construction}, ',') > 0 AND
  locate({ats-gen 4.0-construction}, '.') = 0,
  concat('$ ', {ats-gen 4.0-construction}, '.00'),
  
  // Negative integer with commas: -1,234 → "-$ 1,234.00"
  locate({ats-gen 4.0-construction}, '-') = 1 AND
  locate({ats-gen 4.0-construction}, ',') > 0 AND
  locate({ats-gen 4.0-construction}, '.') = 0,
  concat('-$', substring({ats-gen 4.0-construction}, 2, strlen({ats-gen 4.0-construction}) - 1), '.00'),
  
  // ═══════════════════════════════════════════════
  // DECIMALS (Has period)
  // ═══════════════════════════════════════════════
  
  // Small decimal: 1.23 to 999.99 → "$ 123.45"
  locate({ats-gen 4.0-construction}, '-') = 0 AND
  locate({ats-gen 4.0-construction}, '.') > 0 AND
  locate({ats-gen 4.0-construction}, ',') = 0 AND
  strlen({ats-gen 4.0-construction}) <= 6,
  concat('$ ', 
    split({ats-gen 4.0-construction}, '.', 1), 
    '.', 
    split({ats-gen 4.0-construction}, '.', 2),
    ifelse(strlen(split({ats-gen 4.0-construction}, '.', 2)) = 1, '0', '')),
  
  // Negative small decimal: -1.23 to -999.99 → "-$ 123.45"
  locate({ats-gen 4.0-construction}, '-') = 1 AND
  locate({ats-gen 4.0-construction}, '.') > 0 AND
  locate({ats-gen 4.0-construction}, ',') = 0 AND
  strlen({ats-gen 4.0-construction}) <= 7,
  concat('-$', 
    substring(split({ats-gen 4.0-construction}, '.', 1), 2, strlen(split({ats-gen 4.0-construction}, '.', 1)) - 1),
    '.', 
    split({ats-gen 4.0-construction}, '.', 2),
    ifelse(strlen(split({ats-gen 4.0-construction}, '.', 2)) = 1, '0', '')),
  
  // Large decimal: 1000.00+ → "$ 1,234.56"
  locate({ats-gen 4.0-construction}, '-') = 0 AND
  locate({ats-gen 4.0-construction}, ',') = 0 AND
  locate({ats-gen 4.0-construction}, '.') > 0 AND
  strlen({ats-gen 4.0-construction}) > 6,
  concat('$ ', 
    substring(split({ats-gen 4.0-construction}, '.', 1), 1, strlen(split({ats-gen 4.0-construction}, '.', 1)) - 3),
    ',',
    substring(split({ats-gen 4.0-construction}, '.', 1), strlen(split({ats-gen 4.0-construction}, '.', 1)) - 2, 3),
    '.',
    split({ats-gen 4.0-construction}, '.', 2),
    ifelse(strlen(split({ats-gen 4.0-construction}, '.', 2)) = 1, '0', '')),
  
  // Negative large decimal: -1000.00+ → "-$ 1,234.56"
  locate({ats-gen 4.0-construction}, '-') = 1 AND
  locate({ats-gen 4.0-construction}, ',') = 0 AND
  locate({ats-gen 4.0-construction}, '.') > 0 AND
  strlen({ats-gen 4.0-construction}) > 7,
  concat('-$', 
    substring(split({ats-gen 4.0-construction}, '.', 1), 2, strlen(split({ats-gen 4.0-construction}, '.', 1)) - 4),
    ',',
    substring(split({ats-gen 4.0-construction}, '.', 1), strlen(split({ats-gen 4.0-construction}, '.', 1)) - 2, 3),
    '.',
    split({ats-gen 4.0-construction}, '.', 2),
    ifelse(strlen(split({ats-gen 4.0-construction}, '.', 2)) = 1, '0', '')),
  
  // Decimal with commas: 1,234.56 → "$ 1,234.56"
  locate({ats-gen 4.0-construction}, '-') = 0 AND
  locate({ats-gen 4.0-construction}, '.') > 0 AND
  locate({ats-gen 4.0-construction}, ',') > 0,
  concat('$ ', 
    split({ats-gen 4.0-construction}, '.', 1),
    '.',
    split({ats-gen 4.0-construction}, '.', 2),
    ifelse(strlen(split({ats-gen 4.0-construction}, '.', 2)) = 1, '0', '')),
  
  // Negative decimal with commas: -1,234.56 → "-$ 1,234.56"
  locate({ats-gen 4.0-construction}, '-') = 1 AND
  locate({ats-gen 4.0-construction}, '.') > 0 AND
  locate({ats-gen 4.0-construction}, ',') > 0,
  concat('-$', 
    substring(split({ats-gen 4.0-construction}, '.', 1), 2, strlen(split({ats-gen 4.0-construction}, '.', 1)) - 1),
    '.',
    split({ats-gen 4.0-construction}, '.', 2),
    ifelse(strlen(split({ats-gen 4.0-construction}, '.', 2)) = 1, '0', '')),
  
  // Fallback for unmatched patterns
  'N/A'
)
2

This is a snapshot of the dashboard.

image
image1051×520 88.6 KB

3

Hi @markusen,

That is one giant ifelse() statement. From how I understand, you are trying to convert text/numbers into a currency while handling the formatting within the formula. With Quick Sight, it is better to logically to convert your data to a number and then format the field outside of the calculated field rather than through the calculated field formula. Quick Sight does not allow much formatting options to be done through a calculated field which may be why your formula will result in slow performance. I would recommend to simply format the visual by clicking the 3 dots next to the field and interact it through the pop up box as seen in the screenshot below.

image
image298×151 8.45 KB

Now while I am not in your exact environment, I did try to create a simpler calculated field formula that you can try out.

ifelse(
  isNull({ats-gen 4.0-construction}) OR trim({ats-gen 4.0-construction}) = '', null,
  toDecimal(
    replace(
      replace(
        replace(trim({ats-gen 4.0-construction}), '$', ''),
      ',', ''),
    ' ', '')
  )
)

By simplifying the formula, the goal is that this can easily be applied to 50+ columns and ideally should not slow down performance. However, I do want to add that I am not able to test out this formula so am unsure if this will work as intended.

With all that said, I do want to add that quick sight’s limitations may not allow this to work. So, if you know that your original calculated field formula works, then you may have to accept the affected performance given all that you are trying to achieve.