Negative Numbers in Brackets

Ask the Community Get Answers
1

Hi Folks

Can’t find any posts on this.

I would like to be able to show Negative Numbers in Bracket format

ie (1.5) instead of -1.5

Hoping someone has done this already?

cheers
Mike

2

Hi @Michael

You can do this by adding a number equation field with this in it

-{Your NegativeField}

Then at the bottom you can select custom prefix/suffix
Before (
After )

Craig

3

Thanks for your prompt reply.

I have a P&L field, so both positive and negative numbers, which I sum in a Pivot Table.

Your suggestion adds brackets to all of the numbers, but I may not have understood your advice.

4

If you are wanting to change it to (1) instead of -1 in a display field or a grid etc, then Craig’s advice is heading in the right direction. probably use =abs({your field}), then set up a third short text field which is your display field - that is conditional such that if its negative it uses the equation, otherwise it uses the original number.

But if you want this to show up in a pivot table, graph, etc then that wont be helpful. You will need some javascript to achieve this. Maybe something like below. Thank ChatGPT for the wisdom …

This is the final edit, I hope - this version also handles currency properly, as per below. Refer all future requests to ChatGPT.

$(document).on('knack-view-render.any', function(event, view, data) {
  function reformatNegativeCell(cell) {
    let $cell = $(cell);
    let html = $cell.html();
    let text = $cell.text().trim();

    // Check if it's a negative number (with optional $)
    if (/^-\$?[\d,]+(\.\d{1,2})?$/.test(text)) {
      // Remove any minus signs in HTML (safely)
      let cleanedHtml = html.replace(/-+/g, '');

      // Wrap the entire visible number in parentheses
      // We do this only once: wrap the first full number sequence
      let updatedHtml = cleanedHtml.replace(/([\$]?\s*[\d,]+(?:\.\d{1,2})?)/, '( $1 )');

      $cell.html(updatedHtml);
    }
  }

  // Format pivot cells
  $('.kn-pivot-calc').each(function() {
    reformatNegativeCell(this);
  });

  // Format summary row cells
  $('tr.kn-table_summary td').each(function() {
    reformatNegativeCell(this);
  });
});

image
image252×353 8.48 KB

displays as

image
image544×194 4.32 KB

5

Ah I see.

Ignore this I just realised it won’t work
Craig

6

Many thanks Leigh, much appreciated.