Limit decimal points of percentage columns in chart view

[maxumn]

Hello All, I am using a ‘col series [stack]’ chart to display a series of percentage columns. For readability, I have configured the percent columns to limit the number of decimal points to two. The column values are reflecting this configuration when displayed in a ‘detail’ view, but in the chart view it appears to be showing up to 16 decimal points.

Any ideas to address this? I saw another thread where it was suggested to change them to decimal columns, but I want them to display as percentages for clarity.

[WillowMobileSys]

What datasource are you using? What data type is used for this column in that data source?

Is this value calculated in AppSheet? Somewhere else? Not at all?

[maxumn]

I’m using Google Sheets as the data source. The value is calculated in AppSheet using an App Formula that divides two decimals. The AppSheet column is configured as a Percent column with 2 decimal digits.

[WillowMobileSys]

If you use the sheet solely as a datasource, it is highly recommended that ALL columns be defined as “General Text”. This prevents several potential datatype issues.

If you must define a data type in the sheet because it is used elsewhere, keep in mind that AppSheet stores the raw value as Decimal for a Percent column in AppSheet. You would then want the sheet column defined as “Decimal” with 2 decimal places to match your configuration in the app.

Make the above changes and then force your “bad” value to be recalculated and stored again. That should rectify the extra decimal positions.

Any other columns that do not need to be type defined for external use, define them explicitly as “General Text” (not automatic)!!!

I hope this helps!

[maxumn]

I just changed the formatting for that whole sheet to ‘Plain Text’, but the problem persists.

For what it’s worth, the values in the datasource appear to be limited to two decimal points. When I inspect the cell, it shows two decimals, when I export the sheet as a CSV the values in the resulting file are limited to two decimals. The problem persists in the chart view.

It is as though AppSheet is performing the App Formula discreetly (dividing the two decimals) for the chart instance and not applying the two decimal limit.

If you have other suggestions or questions, I am eager to hear them. Thank you!

[WillowMobileSys]

FYI, I just realized that your initial screenshot of the column configuration is for a DIFFERENT column - not the problem column. Make sure the problem column is ALSO configured to two decimal places.

@maxumn

I just changed the formatting for that whole sheet to ‘Plain Text’, but the problem persists.

After doing this, you ALSO need to either have the value recalculated OR reload the app entirely. It’s possible that AppSheet servers are still carrying the bad value.

For the Chart view, it should only be displaying the value - not reperforming the calculation.

The only other thing I can suggest is to wrap your calculation with the DECIMAL() function to ensure it is indeed a decimal value returned.

If none of the above corrects the problem, then you will need contact AppSheet Support.

[maxumn]

Thank you, apologies for the discrepancy in the screenshots, there are 5 very similarly configured columns. That said, both the column they are all configured to two decimal points, and in different instances they each can show the long decimal string. Here a couple more screenshots demonstrating that.

I forced the recalculation and cleared cache and reloaded the whole app. The problem persists.

I appreciate your help, and I’ll reach out to AppSheet support.

[WillowMobileSys]

Can you show an example calculation you are using for each column? Maybe that might highlight something unusual. Other than that I’m out of ideas!

[maxumn]

Sure! It’s somewhat complicated by the IF function, which is included because detailed data doesn’t exist prior to a certain date. For those date ranges these percentages were manually entered. Please let me know if this raises any questions.

[WillowMobileSys]

Ah, ok. I don’t know if this will help but one more thing to try.

It may be the DECIMAL() functions are NOT helping in this use case. Remove them. Instead, multiply the denominator by 1.0. This forces that COUNT value to be treated as decimal which in turn, when the division is performed, treats the entire result as decimal. For good measure you can multiple BOTH COUNT values by 1.0.

In case you are not aware, when you divide two NUMBER type values, AppSheet will by default also return a NUMBER type value that has been rounded - I think rounded as opposed to truncated.

If that doesn’t help, then I am officially our of ideas.

[Steve]

The problem is with the graphing tool. Notice:

0 is also misformatted. The tool is doing its own formatting of whatever internal representation AppSheet has for the values. I suspect the best you could do is force the value to two decimal points: (FLOOR(value * 100.0) * 1.0 / 100.0).

• value * 100.0 moves the decimal places you want to the left of the decimal point. 3.5700...03 becomes 357.00...03.
• FLOOR(...) removes the decimal point and anything after. 357.00...03 becomes 357.
• ... * 1.0 converts the Number returned by FLOOR() to a Decimal. 357 becomes 357.0.
• (... / 100.0) shifts those wanted decimal values back into position. 357.0 becomes 3.57.

[maxumn]

Thanks, Steve.

I would agree this must have something to do with the graphing tool. It’s quite baffling actually, given the IF function in my App Formulas has these columns only calculate the value for rows where the date range is after a certain point. For the earlier date ranges, these columns reference manually entered percentages, which of course conform to my desired 2 decimal places. Even for these years, the chart shows extended (and necessarily inaccurate) decimals. Bizarre.

Here’s what the values look like in my datasource Google Sheet. Only Financial Years 2025 and 2026 are actually being calculated, the preceding years should just directly be referencing this data:

Here’s an example of the chart corrupting some of those hard written percentages:

Here’s my attempt to implement your fix. Take the Civil Service column as an example. Here are my App Formula’s before and after.

BEFORE:

IF(
[Start Date] >= “7/1/2024”,
COUNT(
FILTER(
“Intake Questionnaire”,
AND(
[Start Date] <= [Timestamp],
[End Date] >= [Timestamp],
[meetings count] > 0,
[job class] = “Civil Service”
)
)
)
/ COUNT(
FILTER(
“Intake Questionnaire”,
AND(
[Start Date] <= [Timestamp],
[End Date] >= [Timestamp],
[meetings count] > 0,
ISNOTBLANK([job class])
)
)
),
[Visitor Job Class (Civil Service)]
)

AFTER:

(
FLOOR(
IF(
[Start Date] >= “7/1/2024”,
COUNT(
FILTER(
“Intake Questionnaire”,
AND(
[Start Date] <= [Timestamp],
[End Date] >= [Timestamp],
[meetings count] > 0,
[job class] = “Civil Service”
)
)
)
/ COUNT(
FILTER(
“Intake Questionnaire”,
AND(
[Start Date] <= [Timestamp],
[End Date] >= [Timestamp],
[meetings count] > 0,
ISNOTBLANK([job class])
)
)
),
[Visitor Job Class (Civil Service)]
)
* 100.0
)

1.0
/ 100.0
)

Unfortunately, this is having some unexpected consequences. Note the different values in my table (e.g. the Civil Service and Student Employees columns are showing 00 after the decimal point):

I think I might be ready to throw in the towel, restore my app to an earlier state, and try to use Quick Chart for this. It seemed like such a straightforward use that I figured I could actually use the built in charting feature, but… I’m losing patience.

[WillowMobileSys]

I was playing with the COUNT division trying to reproduce the issue - not successful yet. I think AppSheet has corrected some of the earlier issues where division of Numbers in an expression are rounded before acquiring the final result - so some of the previously suggested “known” solutions may not have any effect. The results are still definitely rounded if stored in a Number type column.

Here is another simple thing to try, temporarily re-define the column from Percent to Decimal. Do the values in the chart show correctly then? If so, then a workaround solution might be to add an additional Decimal column and assign it the value from the Percent column - Percent column for display in views and Decimal column for use in the charts.