Percent Calculation not calculating, returning 0

AppSheet AppSheet Q&A
1

For some reason the completion rate is return 0. I have two tables: projects and project_tasks. I’m getting back the correct # of tasks by status, but when I do [_THISROW].[Completed Tasks]/[_THISROW].[Total Tasks] it returns 0.

Here are the raw #'s

DayToDataInc_1-1726496223761.png
DayToDataInc_1-1726496223761.png201×298 5.67 KB

Here are my field definitions
Total Task

DayToDataInc_0-1726496157629.png
DayToDataInc_0-1726496157629.png460×177 9.52 KB

Total Tasks = COUNT(FILTER(“project_tasks”, [project_id] = [_THISROW].[id]))
Open Tasks = COUNT(FILTER(“project_tasks”, AND([Status] <> “Done”, [project_id] = [_THISROW].[id])))
Completed Tasks = COUNT(FILTER(“project_tasks”, AND([Status] = “Done”, [project_id] = [_THISROW].[id])))
Completion Rate = [_THISROW].[Completed Tasks]/[_THISROW].[Total Tasks]

When I test the expressions, the Total Tasks, Open Tasks and Completed Tasks are correct

DayToDataInc_2-1726496802159.png
DayToDataInc_2-1726496802159.png1726×359 33.6 KB

I switched the type between decimal and percentage. Still nothing.

What am I missing??

2

Because the Task columns are defined as NUMBER, the resulting expression is attempting to return a like NUMBER value as well. The decimal result is less than 0.5 so the NUMBER result rounds DOWN to zero.

Try this expression instead:

([_THISROW].[Completed Tasks] * 1.0) / ([_THISROW].[Total Tasks] * 1.0)

You probably don’t need to multiply both operands. I do … just because!

3 Likes
3

Please take a look at the relevant tip by @Steve

Fix-for-expression-giving-0-or-missing-decimal-part

2 Likes
4

Worked like a charm :grinning_face:

DayToDataInc_0-1726499557871.png
DayToDataInc_0-1726499557871.png160×169 1.88 KB

1 Like
5

@Suvrutt_Gurjar @WillowMobileSys

Related to this, I’m seeing some weirdness happen.

Here is the video to help illustrate what’s going on: https://drive.google.com/file/d/1g8-ctYhPgVe7shzLB1qwoPknBUX4BF9U/view?usp=drive_link

So I’m calculating the % complete, sometimes it works and sometimes it defaults to BLANK.

I’m not sure why this is occurring. Even in the individual virtual column where I’m calculating the percentage rates, sometimes it shows the correct decimal and then sometimes it set is to 0?

Any thoughts?

6

Two things:

  1. In the Completion Rate and the Completion Details fields, remove the [_THISROW] qualifier. I am not sure but I think it may be interfering with the Virtual column recalcs. I didn’t think of this before or would have suggested it then.

  2. Make your Completion Rate column defined as a Percent. The percent value you are showing is incorrect. You made need to adjust the Completion Details expression when you do this.

I hope this helps!

1 Like