Aggregate by user -> date -> some metric. Rollup doesnt seem to work?

Ask for Help
1

I am creating an app where users can enter their step count daily and see how their friends are doing in total as well.

For this to work I need to

  1. Show each user their step count only
  2. For each user group step counts by date

For 1, I am using row owner. When I do this the entries are limited to that users row. However when I do a rollup, the rollup also counts all the rows for that user.

For 2, How do I aggregate by date.

User table: User, Email, Total steps
Step table: User, Email, Date, Steps. Each date can have multiple entries.

This might sound like a complicated question. Let me know if its confusing and i can clarify. Hope someone can help.

2

you are trying to do this in Glide table or Google Sheet?
In glide, you have to rollup user specific column with steps data… if you put step data to non user specific column… it will count all users

3

Currently using Glide table. But can change to sheet if that works better too

4

in GS all you have to do is use countif formula, and in glide you have to put steps count in user specific column and roll up sum

5

Sorry how does countif aggregate by date?

6

count if the date is …

7

how do you keep steps data? each record separate row?

8

Yeah but users can enter multiple times in a day. I think I am getting what you mean. Let me try and come back :slight_smile:

9

so you can use formula… filter(sum(…), user=…, date=…))

10

in Glide to sum each user and day separately… thats very complicated task, for total steps just roll sum on USC

11

Since you are sharing data with other users, Row Owners would not work well for you here, because this will prevent users from seeing other user’s data. Same with user specific columns. They also prevent others from seeing the data. I’m guessing with what you want to achieve, I would avoid row owners and user specific columns.

To get counts, what I would do is create a template column that combines the Email and Date columns.
Then you can create a multiple relation that takes that template column and relates it back to itself.
Finally you can create a rollup column that sums the steps through that relation. That will give you a total number of steps for each user/date.

Now if you’re looking to only show the aggragated total for each date, then I would also consider adding a Row ID column to your step table. Using that row id, then create a single relation that links the email/date template back to itself. Create a lookup column the retrieves the Row ID. Finally create an IF column to compare the row’s Row ID to the lookup Row ID. If they match, then return true.
What this does is only put TRUE on the first row for a user/date. So if they record steps multiple times in one day, only the first instance will have a TRUE, but every user/date row will have their total for the day. This allows you to filter only the TRUE rows, so you don’t see multiple entries per day.

This might cover most of what you want, unless you’re looking for some additional breakdown or summarization of data.

1 Like