How to Format Numbers as: 120K, 15.2M, 2.33B, 7T Using C9QL
In Javascript the format for truncating and rounding values with a K,M,B,T symbol at the end is 0a, 0.0a, 0.00a depending on the desired number of decimals. Many of the widgets in Knowi support these formats in the data formatter found in widget settings. However, some features such as CSV export and referencing values in Custom HTML widgets, and data tokens requires the user to use C9QL to achieve these figures. Unlike the JS library, C9QL does not support these formats in functions such as Number_Format or Currency_Format. However with the following functions the user can achieve the desired format. It is important to remember that once the field is formatted this way the numbers will now be in a string format, this will prevent the user from being able to do any further arithmetic functions on this field, thus this should be reserved for the final steps of the C9 transformation. The formulas are as follows:
0a:
Case when
length(SUBSTRING_BEFORE(<Field>,.)) >=4
and length(SUBSTRING_BEFORE(<Field>,.)) <=6
then concat(SUBSTRING_BEFORE(round(<Field>/1000,0),.),K)
when
length(SUBSTRING_BEFORE(<Field>,.)) >=7
and length(SUBSTRING_BEFORE(<Field>,.)) <=9
then concat(SUBSTRING_BEFORE(round(<Field>/1000000,0),.),M)
when
length(SUBSTRING_BEFORE(<Field>,.)) >=10
and length(SUBSTRING_BEFORE(<Field>,.)) <=12
then concat(SUBSTRING_BEFORE(round(<Field>/1000000000,0),.),B)
when
length(SUBSTRING_BEFORE(<Field>,.)) >=13
and length(SUBSTRING_BEFORE(<Field>,.)) <=15
then concat(SUBSTRING_BEFORE(round(<Field>/1000000000000,0),.),T)
Else Round(<Field>,0)
END
0.0a:
Case when
length(SUBSTRING_BEFORE(<Field>,.)) >=4
and length(SUBSTRING_BEFORE(<Field>,.)) <=6
then concat(round(<Field>/1000,1),K)
when
length(SUBSTRING_BEFORE(<Field>,.)) >=7
and length(SUBSTRING_BEFORE(<Field>,.)) <=9
then concat(round(<Field>/1000000,1),M)
when
length(SUBSTRING_BEFORE(<Field>,.)) >=10
and length(SUBSTRING_BEFORE(<Field>,.)) <=12
then concat(round(<Field>/1000000000,1),B)
when
length(SUBSTRING_BEFORE(<Field>,.)) >=13
and length(SUBSTRING_BEFORE(<Field>,.)) <=15
then concat(round(<Field>/1000000000000,1),T)
Else Round(<Field>,1)
END
0.00a:
Case when
length(SUBSTRING_BEFORE(<Field>,.)) >=4
and length(SUBSTRING_BEFORE(<Field>,.)) <=6
then concat(round(<Field>/1000,2),K)
when
length(SUBSTRING_BEFORE(<Field>,.)) >=7
and length(SUBSTRING_BEFORE(<Field>,.)) <=9
then concat(round(<Field>/1000000,2),M)
when
length(SUBSTRING_BEFORE(<Field>,.)) >=10
and length(SUBSTRING_BEFORE(<Field>,.)) <=12
then concat(round(<Field>/1000000000,2),B)
when
length(SUBSTRING_BEFORE(<Field>,.)) >=13
and length(SUBSTRING_BEFORE(<Field>,.)) <=15
then concat(round(<Field>/1000000000000,2),T)
Else Round(<Field>,2)
END
Please sign in to leave a comment.
0 comments