Automatic percentage calculation

[claudioux]

Hi there everybody.

I'm building a project capacity-managment app, and I'm trying to understand if there's a way for the app to automatically calculate the % of "Capacity left" based on the % of "Current load" and viceversa, especially if this works adding and deleting data within the app directly.

The max capacity is 100%: there are 3 mid-steps wich are 25%, 50% and 75% (apart from full capacity - 100% - and no capacity - 0%).

When someone in the team has a "Current load" of work of 25%, their "Capacity left" must show 75%. Is there a way to automate that? I understand it's in the Columns section, but can't quite get it sorted.

I have a Sheet with the two columns (Current load and Capacity left), the latter with a function =(100- .. )/100, which results in a %. The "Current load" column simply has 0, 25, 50, 75 or 100 - which is obviously reflected as a number and not as a percentage in the app.

Hope it's clear enough, thanks 🙂

[WillowMobileSys]

There isn't a pre-built mechanism to handle this for you but there are a couple of columns that can help implement what you need.

First, there is a Progress column (see below) that will show a pie chart image to indicate the progress.   You will need to create an expression to determine the progress level and set the proper Enum value.  

Second, there is a percent column.  Same idea applies where you would need to use an expression to decide what percent level you want to set

You could combine the two and show both a percent level and the visual progress chart.

Column definition of Progress

     

Example of how Progress column is displayed

[claudioux]

Thanks for the reply.

So even using this implementation I'm not going to be able to perfectly automate the percentage "switch" between Capacity and Load when one or the other variates, without operating directly on the Sheet, right?

[WillowMobileSys]

FIrst, I don't recommend performing any computations in the sheet directly, unless you absolutely have to.

Yes, you can automate it in AppSheet - as long as you know what the formula should be to determine the percentage level.   Then you just apply that as an App Formula in a normal column or a Virtual Column. 

Use a normal column as the Progress column if all the values to compute the progress level are in the same data row.

Use a Virtual Column, type = Progress, if the formula relies on values from different tables.

Do you know how to compute the [Current Load] as a percentage?  I'll assume [Capacity Left] = 100% - [Current Load].

[claudioux]

Thanks again. I am kind of understanding the point here, but still I'm not getting the result I want - sorry about that but I literally discovered AppSheet two days ago and have been given a full project to do with it.

I've set the [Capacity Left] = 100% - [Current Load]  formula directly on the Current load column, setting it as a percentage (and by the way, it shows me back an error saying it's not the expected expression format). Now: does this column in the sheet need to be empty from data? 

What about the Capacity left column? That needs to be set as Progress, I imagine: the data in this case has to be in the sheet, right? Otherwise where does it take data for the formula from?

All I'm trying to understand is how I can input a value (0, 25, 50, 75, 100) on Current load, so that the Capacity left is automatically calculated.

Would you mind being more specific? I'd deeply appreciate it. Thanks a lot again for your help.

[Steve]

---

@claudioux wrote:

I've set the [Capacity Left] = 100% - [Current Load]  formula directly on the Current load column, setting it as a percentage (and by the way, it shows me back an error saying it's not the expected expression format). Now: does this column in the sheet need to be empty from data? 

---

Your expression, [Capacity Left] = 100% - [Current Load], is bad in several ways. I suggest you spend more time reading the help docs and just tinkering. You're clearly in over your head and have some learning to do before you can make much progress on your app without a lot of hand-holding.

[SkrOYC]

Just extra tip, percentages when using the percent column are factors, from 0 to 1. So it's a decimal column under the hood, not a column in which you would expect to use 0%-100% since that's just the way we are used to read it.

Column data types - AppSheet Help
Math expressions - AppSheet Help
Apps using Price, Percent, and Decimal formats - AppSheet Help