Average by sub-type

Paul_Janson
Participant III

I’m trying to create a virtual column that gives an average score for each user based on a sub-group categorization in another column. In the screenshot below, the first four columns are from my actual spreadsheet, the last two columns, Progress and Category Ave, are hard entered to illustrate what I’m trying to accomplish. The Category Ave column represents the virtual column I’m trying to create- an average of the Progress column per Category type.

What I have so far-
AVERAGE(SELECT(Goal[Progress],
AND([User]=[_THISROW].[User],
…???
))

Struggling to see the light…

Solved Solved
0 29 1,120
1 ACCEPTED SOLUTION

Need a bit of complex workaround to achieve your goal, but this is surely doable.

First, please prepare the new single independent table where you have two fields, user , category. This table is prepared for the purpose of distinct set of User-Category combination. For this table, select both column as key value then save the setting. Then I believe Appsheet will generate the auto fields as key, where the combination of two field value is made as key of this table.

Then, Create action on the table where you record all the activities, where have user and category value per row. The required action is add new row to another table using value from this row. Pass the value of user and category only to the table you just create above, and place ths action “On save” of the form of the table.

Upon creating and editing the form to record of the activities, the action is fired and add row to new table. In case there are exisiting row storing the same value, combination of user and caregory, then Appsheet should overwrite the existing value as both share the same key value. This stay the new table holding unique set of user+category all the time.

Then for this newly created table, add virtual column

AVERAGE(SELECT(Goal[Progress], AND([User]=[_THISROW].[User],[Category]=[_THISROW].[Category]))

I believe this will fields the values what you want.

View solution in original post

29 REPLIES 29

Hey there @Paul_Janson

Maybe try:

AVERAGE(SELECT(Goal[Progress],
([Category]=[_THISROW].[Category])))

Note that Progress column type should not be Text. It should be any numeric type like Decimal.

Paul_Janson
Participant III

Thanks for your reply.

The table that this virtual column is in does not have a Category column, unfortunately. I have a virtual column called Program Categories, which lists all categories associated with a program, and Active Categories, which lists all categories for which a user is actively enrolled in. These two columns are hard entered in blue in the screenshot for illustrative purposes.

[Category]=[_THISROW].[Category] would need to be something like IN([Category],[_THISROW].[Active Categories]. This returns the same number, which is an average of all Progress values, and the alternative- IN([Category],[_THISROW].[Program Categories] for some reason returns 0’s.

And yes, Progress type is Percent

@Paul_Janson

Can you tell me what are the names of the tables and send a screenshot of each?

Hi Eloy,

Yes, below are table names and screenshots. I’ve taken a new approach to solving this that has gotten me closer but presented a different set of problems.

Let’s start with the desired outcome- in this Fitness Leaderboard view I want only one row displayed for each user. Here you can see there are multiple rows being presented for each user-

This view is pulling from the Goal table, which is really the only table involved-

The Goal table has multiple Activity (Name) entries per Category, thus the multiple rows displaying on the view. If a user has three Activities in a Category (in this case Fitness), there will be three rows showing in the view.

There must be a UNIQUE() function entered somewhere, right? I’ve tried using it in the virtual column and in the slice row selection. Goal[Category Progress Num] is the virtual column generating the number in the chart-

3X_4_4_4450706e7064c9f6748c66eeb191d503f6095880.png

And the Fitness Leaderboard slice row filter condition-
[Category]=“4d7a5c48”
(key for Fitness in the Category Table).

Thanks for taking a look! I appreciate it.

@Paul_Janson congrats! It seems you solved it.

Glad to help any time!

Actually, no. I’m still stuck. In the first screenshot you can see that there is not a unique listing for each user. The issue is as follows-

The Goal table has multiple Activity (Name) entries per Category, thus the multiple rows displaying on the view. If a user has three Activities in a Category (in this case Fitness), there will be three rows showing in the view.

We’re trying to figure out how to use MAXROW() to pull just one row but with no success. I’ve tried UNIQUE() at the virtual column and slice to no effect, though it’s possible I’m doing it incorrectly.

Any thoughts?

@Paul_Janson
I would create a virtual column in the Users table with the expression:

AVERAGE( SELECT( Goal [Progress], [User] = [_THISROW].[User]))

Then I would generate the view pulled from the Users table instead of the Goal table.
That way you should see one User per row in the view.

Thanks Eloy, that does solve the “one User per row in the view” problem, but it creates an average of ALL Category Progress Numbers, merging all distinct Category Progress numbers into one.

Here’s an illustration of the scenario-

Goal[Progress] is the Activity Score
Goal[Category Progress Num] is the Category Score
Enrollment[Program Progress Num] is the Program Score

Each is simply an average of the child scores. But to use AVERAGE( SELECT( Goal [Progress], [User] = [_THISROW].[User])) in the User (or Enrollment) table as you suggested loses the distinct Category Score and basically returns another Program Score.

The nested list complexities this created in the Enrollment table I found impossible to deal with. This is why I switched to trying to put it onto the Goal table, where instead of facing the problem of unpacking nested lists I need to try figure out how to consolidate multiple rows into one. It seemed easier, something a simple UNIQUE expression should solve, but it’s proving to be much more stubborn.

I’m still pretty new at this, definitely willing to try again with the Enrollment table if you have strategies for dealing with “lists of lists”. I do have a virtual column Enrollment[Active Categories] which is a list of all of active Categories of each user.

Thanks again for spending some time with this, looking forward to paying it forward someday once I get a better handle on things.

Need a bit of complex workaround to achieve your goal, but this is surely doable.

First, please prepare the new single independent table where you have two fields, user , category. This table is prepared for the purpose of distinct set of User-Category combination. For this table, select both column as key value then save the setting. Then I believe Appsheet will generate the auto fields as key, where the combination of two field value is made as key of this table.

Then, Create action on the table where you record all the activities, where have user and category value per row. The required action is add new row to another table using value from this row. Pass the value of user and category only to the table you just create above, and place ths action “On save” of the form of the table.

Upon creating and editing the form to record of the activities, the action is fired and add row to new table. In case there are exisiting row storing the same value, combination of user and caregory, then Appsheet should overwrite the existing value as both share the same key value. This stay the new table holding unique set of user+category all the time.

Then for this newly created table, add virtual column

AVERAGE(SELECT(Goal[Progress], AND([User]=[_THISROW].[User],[Category]=[_THISROW].[Category]))

I believe this will fields the values what you want.

Ok, this is looking very promising. I don’t have much experience with Actions. I created a new table, Category_Dash-

I set up two columns both marked as key that created a new concatenated key-

I created a new Action, AddCategory_Dash

Here’s where I’m stuck. Set These Columns… what should I put here?

First, simply

[User]

[Category]

You dont need to push any expression right here.

3X_7_5_751aef3c0acb4fae41b600b8c4a580c08ab3e474.png

And hide this action.

Ok, I added the columns to the formula.

What do you mean, “Hide this action”?

I’ll add the virtual column now…

Hide action is to select do not display.

Last , go to the form view setting where you let the user input the activities, and

Form saved action, select the action you just created and save.

Basically that s it.

Second you need to add virtual column to this

With expression i adviced to you.

Paul_Janson
Participant III

It populated the new table! Amazing.

Let me change the view to pull from this table and see what we have…

Here you go.

Nothing is impossible with Appsheet.

You can apply grouping by User or user category, or even by KEY (Combination of user and category) to make the better looking to the user on table view.

Enjoy Appsheet.

I just worry about it you have same User + Category combination as well as existing row, appsheet is nicely overwriting rather than append new row?

you have progress var in the original table, but you can regenereate the same bar chart in the new table, i understand you know how to do it.

Paul_Janson
Participant III

Yes I do, thank you.

Ok, first issue- I changed the type of both columns to Text to get rid of an error earlier. Now only the key is showing instead of the user name. When I change it back to Ref I get this error-

Paul_Janson
Participant III

3X_4_4_444779bf23732c6ba87daab6d78e6b5a3a43f0bf.png

Change user , category fields type to TEXT instead of REF

When I do Text I only get the key written as a string, 2nd screenshot

Oh sorry if you have user and category table, then chage the label to the field you want to display.

Currently, ID / Key fiels shold be selected as LABEL.

Paul_Janson
Participant III

This seems to do the trick! Thanks so much!

One thing that surprised me was that the Action only enters a new row in the new table if it is a unique entry. Where does this constraint live? The name of the action is “add a new row using values from this row”. It seems it would add a new row every time there is a new entry in the source table regardless of whether it’s a repeat.

Actually it is adding new row by this type of action. But if there is exisiting row with same key, appsheet is just overwrite. Nothing odd, but twisting this native behavior to achieve your goal this time

But most importantly, now you get to your goal, hopefully.

I see, it simply overwrites it. Very useful! Thanks again, I appreciate you taking the time.

You are welcome.

Paul_Janson
Participant III

I thought I would ask on this thread before adding a new question. When I delete from a parent table, “Is a part of” is working for all child tables except for the one I created here that is populated using an action.

It’s set up in the same way as the other 2 tables, on the REF virtual column for User, but when a User is deleted, the rows associated with that user in the Category_Dash table do not delete. Any ideas?

Top Labels in this Space