Data Format for negative values (Parentheses)

Feature Suggestions
1

Hello,

Is there a way to format negative values to be inside parentheses? Something that a spreadsheet format like $0.00;($0.00) might give you.

Thanks!

3

Hello @hannah.paxton ,

Thank you for raising you issue. At the moment, Holistics does not support custom and conditional formats like that. However, you can achieve it by using our AQL:

case(
  when: value < 0
  , then: concat('($', cast(abs(value), 'text') , ')')
  , else: concat('$', cast(value, 'text'))
)

image
image840×827 45.4 KB

I hope my reply is helpful to you. I also added your use-case to our Product backlog. I will let you know if there are any updates on this request.

4

@Tai_Nguyen - thank you!

This nearly works for our use case, but when casting the currency value in the concat statement, we lose the thousands separators, which are needed here. Appreciate you keeping me in the loop!

5

Hi @hannah.paxton,

Thank for your reply. I found some workarounds but they have some limitation:

  1. Use BigQuery’s format: this workaround could help resolve your issue but it’s not flexible and can only be used as a static dimension (you can not perform aggregation like sum on this column)
    Step 1: Create a thousand_separator_value dimension in your model to format value:
  dimension thousand_separator_value {
    label: 'thoudsand separator value'
    type: 'text'
    hidden: true
    definition: @sql TRANSLATE(
      FORMAT("%'.2f", CAST(abs({{ #SOURCE.value }}) as NUMERIC)),
      ',.',
      '.,'
    )  ;;
  }

then create a dimension in dataset to show correct value:

  dimension financial_number {
    model: your_model
    label: "Financial Number"
    type: "text"
    hidden: false
    description: ""
    definition: @aql case(
      when: your_model.value < 0
      , then: concat('($', your_model.thousand_separator_value , ')')
      , else: concat('$', your_model.thousand_separator_value)
    );;
  }

image
image1137×857 90.4 KB

  1. Manually create thousands of separators before using them in case…when… If your value is greater than 1000000, you have to duplicate the code to handle
case(
  when: cast(revenue, 'int') < 1000
  , then: concat('$', cast(cast(revenue, 'int'), 'text'))
  , when: cast(revenue, 'int') < 10000
  , then: concat(
      '$', 
      cast(div(mod(cast(revenue, 'int'), 10000), 1000), 'text'), ',',
      cast(div(mod(cast(revenue, 'int'), 1000), 100), 'text'),
      cast(div(mod(cast(revenue, 'int'), 100), 10), 'text'),
      cast(div(mod(cast(revenue, 'int'), 10), 1), 'text'),
      )
  , when: cast(revenue, 'int') < 100000
  , then: concat(
      '$', 
      cast(div(mod(cast(revenue, 'int'), 100000), 10000), 'text'),
      cast(div(mod(cast(revenue, 'int'), 10000), 1000), 'text'), ',',
      cast(div(mod(cast(revenue, 'int'), 1000), 100), 'text'),
      cast(div(mod(cast(revenue, 'int'), 100), 10), 'text'),
      cast(div(mod(cast(revenue, 'int'), 10), 1), 'text'),
      )
  , when: cast(revenue, 'int') < 1000000
  , then: concat(
      '$', 
      cast(div(mod(cast(revenue, 'int'), 1000000), 100000), 'text'),
      cast(div(mod(cast(revenue, 'int'), 100000), 10000), 'text'),
      cast(div(mod(cast(revenue, 'int'), 10000), 1000), 'text'), ',',
      cast(div(mod(cast(revenue, 'int'), 1000), 100), 'text'),
      cast(div(mod(cast(revenue, 'int'), 100), 10), 'text'),
      cast(div(mod(cast(revenue, 'int'), 10), 1), 'text'),
      )
  , when: cast(revenue, 'int') < 10000000
  , then: concat(
      '$', 
      cast(div(mod(cast(revenue, 'int'), 10000000), 1000000), 'text'), ',',
      cast(div(mod(cast(revenue, 'int'), 1000000), 100000), 'text'),
      cast(div(mod(cast(revenue, 'int'), 100000), 10000), 'text'),
      cast(div(mod(cast(revenue, 'int'), 10000), 1000), 'text'), ',',
      cast(div(mod(cast(revenue, 'int'), 1000), 100), 'text'),
      cast(div(mod(cast(revenue, 'int'), 100), 10), 'text'),
      cast(div(mod(cast(revenue, 'int'), 10), 1), 'text'),
      )
  , when: cast(revenue, 'int') < 100000000
  , then: concat(
      '$', 
      cast(div(mod(cast(revenue, 'int'), 100000000), 10000000), 'text'),
      cast(div(mod(cast(revenue, 'int'), 10000000), 1000000), 'text'), ',',
      cast(div(mod(cast(revenue, 'int'), 1000000), 100000), 'text'),
      cast(div(mod(cast(revenue, 'int'), 100000), 10000), 'text'),
      cast(div(mod(cast(revenue, 'int'), 10000), 1000), 'text'), ',',
      cast(div(mod(cast(revenue, 'int'), 1000), 100), 'text'),
      cast(div(mod(cast(revenue, 'int'), 100), 10), 'text'),
      cast(div(mod(cast(revenue, 'int'), 10), 1), 'text'),
      )
)

image
image1352×1306 198 KB

then used in final metric:

case(
  when: revenue < 0
  , then: concat('(', formatted , ')')
  , else: formatted
)

These above workarounds are temporary solution at the moment and require many effort to implement and maintain. I will let you know if there are any features from Holistics could solve your issue easier.

6

Thank you for the workaround. I appreciate the thorough response!

7

Hi Folks,

In relation to number formatting, I am setting a financials dashboard and need to put values in thousands without the K at the end and in ()'s for negative numbers. When I remove the K in the code, the formatting breaks and doesn’t stay in thousands. Additionally, I can’t figure out to do formatting for negative numbers. Any idea how to set things up to do the following:

1,000,000 → $1,000
-1,000,000 → ($1,000)

1 Like