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!
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!
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'))
)
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.
@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!
Hi @hannah.paxton,
Thank for your reply. I found some workarounds but they have some limitation:
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)
);;
}
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'),
)
)
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.
Thank you for the workaround. I appreciate the thorough response!
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)