Looking for help with an Expression

AppSheet AppSheet Q&A
1

Hi all, I am looking for help with an Expression.

When documenting meetings on my App I created a Yes/No option that people can select if the meeting was during a scheduled or unscheduled time as I want to track this. I am looking to create a virtual column that will calculate the percentage of meetings completed during the scheduled time.

How do I get the expression to Sum the scheduled meetings divided by the Sum of total meetings? Is there a way to filter this during a specific time frame, quarter to quarter or year to year?

Thank you in advance.

2

Do the sum of scheduled meetings divided by the sum of total meetings, you can:

  • (COUNT(FILTER(“TableName”,[Y/N Column]=TRUE)))/(COUNT(TableName[Key Column]))

If you want to filter, you will need to set up another table that has the filter fields you want and then use to make a slice on your data. There are many useful tutorials on Youtube if you search for AppSheet filter dashboard.

2 Likes
3

Also you may want to have the expression in a real column in say User table or some such table and get it calculated on demand by say an action. With this arrangement, you may not have a sync expensive virtual column that keep calculating on every sync. The downside with real column will be - it will recalculate on demand when the action is executed and not real time.

1 Like
4

You need to have a date column in your table and used it to filter the period ie. a quarter or a year.
Apply the filter to a slice and do the count there.

1 Like
5

Thank you, I do have a date column in my table for documenting the meetings.

6

Thank you for the help. I appreciate it. Here is what my final expression ended up being.

(count(select(Meaningful Interactions[Was this MI part of surge?],[Was this MI part of surge?]=TRUE)))/((count(select(Meaningful Interactions[Was this MI part of surge?],[Was this MI part of surge?]=TRUE)))+(count(select(Meaningful Interactions[Was this MI part of surge?],[Was this MI part of surge?]=FALSE))))

If I want to have it calculate just the rolling last 12 months, any thoughts on what I should add to the formula to have it filter that?

7

Thank you for the help. I appreciate it. Here is what my final expression ended up being. I did decide to add it as a real column instead of a virtual column. I don’t need it to calculate in real time.

(count(select(Meaningful Interactions[Was this MI part of surge?],[Was this MI part of surge?]=TRUE)))/((count(select(Meaningful Interactions[Was this MI part of surge?],[Was this MI part of surge?]=TRUE)))+(count(select(Meaningful Interactions[Was this MI part of surge?],[Was this MI part of surge?]=FALSE))))

If I want to have it calculate just the rolling last 12 months, any thoughts on what I should add to the formula to have it filter that?

8

You could try an expression something like

count(select(Meaningful Interactions[Was this MI part of surge?],AND([Was this MI part of surge?]=TRUE, [Date_Column]>= TODAY()-365))))/((count(select(Meaningful Interactions[Was this MI part of surge?],AND([Was this MI part of surge?]=TRUE , [Date_Column]>= TODAY()-365)))+(count(select(Meaningful Interactions[Was this MI part of surge?], AND([Was this MI part of surge?]=FALSE , [Date_Column]>= TODAY()-365)))

Please pay attention to parenthesis. I might have missed one or added an extra one.

1 Like
9

Thanks for the help! Adding AND with the date column >= TODAY()-365 worked well for the expression.