Pivot table macro not applying decimal place setting

Green, Desmond

I'm using a pivot table and want to simply round the data from a calculated column, to the nearest integer, no decimal places.

Here is the configuration of the pivot table.

It doesn't seem to matter what I enter into the 'Decimal places' setting, the output is at 1 decimal place.

Am I missing something obvious or else how do I work around this?

Stiltsoft support

Hi @Green, Desmond ,

The decimal places are applied to the calculated results. Here your calculated results are 6 and 7 (you get them using the Count operation type). So, the decimal places may be applied only to these numbers.

The per cents are counted automatically by the macro and are set to have one decimal place by default.

The use case for applying the number coming from the decimal places field may be when you have some column with numbers and you choose the Average operation type. Then you may get not integer results like the Count returns but some numbers as 5.4, 6.76 and so on. In this case when you set decimal places as 0, the results will be rounded.

So, you may leave everything as it is for simplicity or use the Table Transformer macro:

SELECT
"Count" AS 'Pass/Fail',
ROUND(SUM(IF(T1.'Pass/Fail' = "Pass", 1, 0))/(SUM(IF(T1.'Pass/Fail' = "Pass", 1, 0)) +
SUM(IF(T1.'Pass/Fail' = "Fail", 1, 0))) * 100, 0) + "% " +
"(" + SUM(IF(T1.'Pass/Fail' = "Pass", 1, 0)) + ")"
AS 'Pass Percent',
ROUND(SUM(IF(T1.'Pass/Fail' = "Fail", 1, 0))/(SUM(IF(T1.'Pass/Fail' = "Pass", 1, 0)) +
SUM(IF(T1.'Pass/Fail' = "Fail", 1, 0))) * 100, 0) + "% " +
"(" + SUM(IF(T1.'Pass/Fail' = "Fail", 1, 0)) + ")"
AS 'Fail Percent'
FROM T1

The result table was transposed (the last checkbox in the Settings tab) to match the outcome from your pivot table. But you may leave it as it is.

Also if you don't have empty cells in your table, you may count the total number of rows as COUNT('Pass/Fail') instead of the (SUM(IF(T1.'Pass/Fail' = "Pass", 1, 0)) +
SUM(IF(T1.'Pass/Fail' = "Fail", 1, 0)) where we count the two statuses separately.

Hope this helps your case.