asked on June 2, 2021
Hi, Team,
We are faced with the problem of formatting numbers on the pivot table and highcharts.
In our current implementation, we apply a specific format for all fields of type number. The problem is that this logic is not suitable for all number type fields, we want to apply it only for fields with the number type, whose values are decimal, and leave integers as they are and do not format them.
As a result, I have the following question, how can we distinguish the type number with integer values from the type number with decimal values. Can we instead of this type introduce two new ones, int and decimal, for example, or can we add a flag somewhere?
Thanks for your advise, Denis
⋅ June 3, 2021
Hello, Denis,
Thank you for reaching out to us.
You can specify different formatting rules for separate measures by associating them using identifiers.
For example, you can create two different formatting rules for integers and floats:
"formats": [{
"name": "float",
"decimalPlaces": 2
},
{
"name": "integer",
"thousandsSeparator": ","
}
]
Now, you can associate measures with corresponding formats using the formatting property:
"slice": {
"rows": [{
"uniqueName": "Item"
}],
"columns": [{
"uniqueName": "[Measures]"
}],
"measures": [{
"uniqueName": "Price",
"aggregation": "sum",
"format": "float"
},
{
"uniqueName": "Quantity",
"aggregation": "sum",
"format": "integer"
}]
}
Here is a JSFiddle for demonstration: https://jsfiddle.net/flexmonster/kmpo1dgv/.
A single formatting rule can be applied to multiple measures.
Please let us know if it works for your case.
Looking forward to hearing from you.
Kind regards,
Illia
⋅ June 4, 2021
I understand that we have opportunity to create two different formats with different settings. Problem is that currently we can't distinguish number integer from number float because we have only number type. We applying some default formatting for each type for the pivot by the API before pivot creation. So, for example, we add currency symbol for the money, some formatting for the time and formatting for the number type (as I mentioned before there is default decimal places for this type = 2). And when we apply this formatting for number type, we apply it for all numbers include integer and add to it not needed .00. So, we need some condition by which we will determine integer and float, and do not apply decimal places to integer, only to float before pivot is loaded. It can be new types of metrics or probably some flag. We just need advice for it. And probably if it's really will increase a complexity of logic and unreadability of code, we will refuse default formatting for number type.
Regards, Denis
⋅ June 4, 2021
Hello, Denis,
Thank you for the details about your use case.
You are right that Flexmonster does not distinguish integer and float numbers as different data types. Currently, there are no know approaches to apply different "basic" formatting for integers and float numbers.
However, we suggest checking out the following workaround based on the customizeCell method. The idea is to replace the unwanted ".00" part after applying the basic rule for all the measures: https://jsfiddle.net/flexmonster/oj6tyr2h/. This approach presupposes using currency symbols and will not remove it both for integer and floating numbers.
Please let us know if this workaround works for your case.
Looking forward to hearing from you.
Kind regards,
Illia
⋅ June 16, 2021
Hello, Denis,
We are reaching out to ask if the suggested workaround works for you.
Feel free to contact us if you have any questions on this point.
Kind regards,
Illia
⋅ June 23, 2021
Hello, Denis,
We are wondering if you had the possibility to try out the provided solution.
Looking forward to your feedback.
Best regards,
Illia