Hi Experts,
I have a table that collects data from different users.
DateTime \ UserID \ Value1 \ Value2 \ etc…
I need to eventually be able to send this data to QuickChart but I need to sum and average them per day and user.
I know how to isolate the needed user (signed user with if then else).
I can format the datetime to hold the date only, but I don’t know how to aggregate the values per date, (like SQL group by or PARTITION BY?)
I will also then need to send to QuickChart only one value per date and not duplicated per each row of the same date.
Any ideas?
@Darren_Murphy @Jeff_Hager @Robert_Petitto @ThinhDinh
You are rolling up (sum, average) the values? Do you have a fixed number “valueN” of values? Any chance you could structure your data differently and have your values accumulate vertically (a new item/row per value) rather than horizontally (a new column per value)?
Also when you say “per day”, do you mean per weekday (Monday, Tuesday, etc.) or per date (1/1/22, 2/1/22, etc.)?
Does your date range have a fixed start and end date, or will these be user selectable?
If user selectable, will there be a maximum allowed range? ie. one month, six months, one year, etc?
This app is running and the Client asked to add charts so restructure is not an easy option/
I don’t have a fixed number of values it depends on the user.
the day is a date
The data is collected by forms filled by the user and the date is a DateTime stamp.
Nothing fixed or known
Well that makes it tricky. So a user could potentially specify a date range that spans 50 years, and you’d be expected to plot a daily chart with 50 years of data? (if there was that much data).
The approach I have in mind involves creating a helper table with enough rows to span the maximum number of days, but if it’s unlimited then that probably won’t work.
1 Like
Not exactly what I need.
I only want to sum the values per one day and display in the chart per day activity.
the user doesn’t choose a date range to display
Do you have a mockup of what the chart should look like?
I’m sure I can help you, I just need a clear picture of what the end goal is, and a rough idea of how the data is structured.
USER | DATETIME | VALUE1 | VALUE2
A | 2022-01-01 18:30 | 100 | 2
A | 2022-01-01 19:30 | 70| 3
B | 2022-01-01 11:30 | 30| 5
B | 2022-01-01 18:50 | 20| 10
B | 2022-01-01 19:50 | 40| 2
B | 2022-01-02 20:50 | 30| 3
B | 2022-01-02 21:50 | 20| 4
What I want is a list For A:
{2022-01-01,170,5}
B:
{2022-01-01,90,17},{,(2022-01-02,50,7}
1 Like
ah, right.
Okay, that’s easy enough. Try this:
- Create a template column that concatenates the User and the Date (without the time).
- Create a multi relation column that joins that template to itself
- Now you can do rollups through that relation, then use another template and a joined list.
Actually, what you need to do is very similar to the solution I describe in the below video:
Have a watch of that - you’ll need to use a very similar technique. In the video, imagine that the Order numbers are your users, and the Items are your dates.
If the above video does not play, please paste the following link into a browser:
https://www.loom.com/share/60f6b4cadc6e4db7bf7cee3d097a0958
4 Likes
Got it ,
Loved the single first RowID trick 
trying it now.
1 Like
What @Darren_Murphy recommends is precisely what I do here:
Be advised that until apps are on the New Computation Model (that Glide Pages is built on), your app has the potential to be really laggy when there is lots of data. The “conditional-relation-to-self” plus joined list over that relation requires quite a bit of processing.
6 Likes