# Rollup with group by date **URL:** \ **Category:** Ask for Help\ **Created:** [June 22, 2022, 12:59pm UTC](https://community.glideapps.com/t/rollup-with-group-by-date/43365 "2022-06-22T12:59:43Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![Lin\_Altshuler](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/lin_altshuler/32/32591_2.png) [@Lin\_Altshuler](https://community.glideapps.com/u/Lin_Altshuler)\ **Post date:** [June 22, 2022, 12:59pm UTC](https://community.glideapps.com/t/rollup-with-group-by-date/43365/1 "2022-06-22T12:59:44Z")
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 ---
**Author:** ![nathanaelb](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/nathanaelb/32/43079_2.png) [@nathanaelb](https://community.glideapps.com/u/nathanaelb)\ **Post date:** [June 22, 2022, 1:11pm UTC](https://community.glideapps.com/t/rollup-with-group-by-date/43365/2 "2022-06-22T13:11:53Z")
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.)? ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [June 22, 2022, 1:18pm UTC](https://community.glideapps.com/t/rollup-with-group-by-date/43365/3 "2022-06-22T13:18:04Z")
> [@Lin\_Altshuler](#): > > 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?) 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? ---
**Author:** ![Lin\_Altshuler](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/lin_altshuler/32/32591_2.png) [@Lin\_Altshuler](https://community.glideapps.com/u/Lin_Altshuler)\ **Post date:** [June 22, 2022, 1:19pm UTC](https://community.glideapps.com/t/rollup-with-group-by-date/43365/4 "2022-06-22T13:19:10Z")
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 ---
**Author:** ![Lin\_Altshuler](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/lin_altshuler/32/32591_2.png) [@Lin\_Altshuler](https://community.glideapps.com/u/Lin_Altshuler)\ **Post date:** [June 22, 2022, 1:20pm UTC](https://community.glideapps.com/t/rollup-with-group-by-date/43365/5 "2022-06-22T13:20:28Z")
The data is collected by forms filled by the user and the date is a DateTime stamp. Nothing fixed or known ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [June 22, 2022, 1:23pm UTC](https://community.glideapps.com/t/rollup-with-group-by-date/43365/6 "2022-06-22T13:23:07Z")
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. ---
**Author:** ![Lin\_Altshuler](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/lin_altshuler/32/32591_2.png) [@Lin\_Altshuler](https://community.glideapps.com/u/Lin_Altshuler)\ **Post date:** [June 22, 2022, 1:25pm UTC](https://community.glideapps.com/t/rollup-with-group-by-date/43365/7 "2022-06-22T13:25:34Z")
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 ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [June 22, 2022, 1:28pm UTC](https://community.glideapps.com/t/rollup-with-group-by-date/43365/8 "2022-06-22T13:28:11Z")
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. ---
**Author:** ![Lin\_Altshuler](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/lin_altshuler/32/32591_2.png) [@Lin\_Altshuler](https://community.glideapps.com/u/Lin_Altshuler)\ **Post date:** [June 22, 2022, 1:30pm UTC](https://community.glideapps.com/t/rollup-with-group-by-date/43365/9 "2022-06-22T13:30:18Z")
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} ---
**Author:** ![Darren\_Murphy](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/darren_murphy/32/47326_2.png) [@Darren\_Murphy](https://community.glideapps.com/u/Darren_Murphy)\ **Post date:** [June 22, 2022, 1:35pm UTC](https://community.glideapps.com/t/rollup-with-group-by-date/43365/10 "2022-06-22T13:35:36Z")
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: https://www.loom.com/embed/60f6b4cadc6e4db7bf7cee3d097a0958 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:_ ```auto https://www.loom.com/share/60f6b4cadc6e4db7bf7cee3d097a0958 ``` ---
**Author:** ![Lin\_Altshuler](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/lin_altshuler/32/32591_2.png) [@Lin\_Altshuler](https://community.glideapps.com/u/Lin_Altshuler)\ **Post date:** [June 22, 2022, 1:51pm UTC](https://community.glideapps.com/t/rollup-with-group-by-date/43365/11 "2022-06-22T13:51:47Z")
Got it , Loved the single first RowID trick 🙂 trying it now. ---
**Author:** ![Robert\_Petitto](https://sea2.discourse-cdn.com/flex002/user_avatar/community.glideapps.com/robert_petitto/32/25193_2.png) [@Robert\_Petitto](https://community.glideapps.com/u/Robert_Petitto)\ **Post date:** [June 22, 2022, 3:53pm UTC](https://community.glideapps.com/t/rollup-with-group-by-date/43365/12 "2022-06-22T15:53:12Z")
What @Darren_Murphy recommends is precisely what I do here: ![CleanShot 2022-06-22 at 11.51.03@2x](https://us1.discourse-cdn.com/flex002/uploads/glideapps/original/3X/5/f/5fd59acf18cf364ff1100a97b85fbd58ab4d8a38.png) 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.