How do I group date periods by week, month, quarter, and year?

I am creating a journaling app. In one tab “Daily Journal” users can enter in a journal entry. In the next tab, I want to give users the option to select ‘Week, Month, Quarter, Year’ and display their daily journal entries grouped together by those times periods. How can I accomplish this?

Thanks in advanced!

You can create a series of Math Columns that pick apart each part of the date in the date column.

For example, you can set up a math formula as follows:
YEAR(date)
MONTH(date)
WEEKNUM(date)

Then you can set your list to group by those math columns.

1 Like

Thank you, sir! I added those math formulas - One column per formula in my sample data. I hope this isn’t a dumb question, I’m still very new to Glide, but how do I set my list to group by those columns?

I assume when users choose “Week” you show the entries of this week, and the same for the others, right?

It would need a lot of columns to achieve what you want, but here’s my idea in addition to Jeff’s.

YEAR

  • Add a math column to derive the year of the entry.
  • Add a math column to derive the current year (YEAR(N) with N being the now value).

Filter the inline list you show when users choose “year” by entry year equals current year.

MONTH

For Month, you would want the year value in there as well so it doesn’t show entries from Jan 2020, for example.

Entry month: YEAR(Entry)*100 + MONTH(Entry) so you would get something like 202112 if the entry is from Dec 2021.

Current month: YEAR(Now)*100 + MONTH(Now) so you would get 202201.

Then use the same filter logic as I said in the year section.

WEEK

With week, you use WEEKNUM.

Entry weeknum: YEAR(Entry)*100 + WEEKNUM(Entry) so you would get something like 202152 if it was posted on the last week of 2021, for example.

Current weeknum: YEAR(Now)*100 + WEEKNUM(Now) so you would get 202201.

Then use the same filter logic as I said in the year section.

QUARTER

This is the most tricky, since we don’t have a built-in function, but we can still use the math column to get it.

Entry quarter: YEAR(Entry)*100 + FLOOR((MONTH(Entry)-1/3)+1) so you would get 202104 if it was posted anywhere between Oct - Dec 2021.

Current quarter: YEAR(Now)*100 + FLOOR((MONTH(Now)-1/3)+1) so you would get 202201.

Then use the same filter logic as I said in the year section.

6 Likes

Thanks ThinhDinh! Yes, it sounds like you understand the idea! Would it be alright if I shared my wireframes? I’d like to elaborate a little more on the specifics of what I’m trying to do. I may not have detailed it clearly.

The user enters in a journal entry every day. At the end of the week, the user is prompted to summarize those entries into an ‘insight’. The user is then taken to a screen that lists the week’s daily journal entries with an input box for them to summarize. This becomes their first ‘weekly insight’. Viewable on the homepage when filtered to “weeks.” This process continues until the end of the month. At this point, the user is prompted to enter a ‘monthly insight’. This time they are presented with the 4 Weekly Insights they made for that month. Just like before, they are asked to summarize those 4 Insights into a monthly insight. This becomes their first ‘monthly insight’. Viewable on the Insights homepage when filtered to “months”

I know that I can filter an inline list by a given time period (and use the logic that you pointed out). However, how do I set up buttons that will automatically filter the master Insights list on the homepage by those time periods? Likewise, how do I set up the Enter Insights form to dynamically show the appropriate time periods entries/insights to allow users to summarize? I feel like we’re so close! But if this is too complicated or not possible, I understand and I appreciate all the help, regardless!

So do you show a list of weeks, and months, and quarters etc for the users to choose when they want to add an insight to that period of time?

This likely requires a complex structure to know which entry is week 1/month 1 etc.

That’s essentially correct. However, the insight entries for those time periods do not need to relate or be tagged to those journal entries in any way. The journal entries are just there to inform the user and are basically ‘read only.’ I would just want to provide them with a uniform way of aggregating them by time period to inspire them to develop insight. I do want the insights to be categorized by time period, though. Maybe I need to write the entries to different tables to know which entry is week1/month 1 etc.?

I don’t think so because that option would not scale well.

I assume week 1/month 1/quarter 1 etc all has to relate to the starting time of the user with your app’s journey?

Saying I start in Nov 2021 then Dec 2021 should be my Month 2 but still Quarter 1, for example?

Yes that is correct. Those time periods should be relative to the user. If it helps, there’s no need to label the weeks, months, etc. sequentially. I just used those titles to indicate they would be the users first entry. But it could just as easily be “October Insight” or “Week ending 01/09/21 Insight”.

I can’t think of an easy way to do this, except you have to label the week, month, quarter and year yourself for each entry, using Glide’s computed columns.

Then, I assume the flow is like this:

  • Users choose Week on the choice component, you show them a list of weeks they have had at least one entry.

  • When they click on a week, you show them the list of entries related to that week, and offer them an option to write an insight for that week.

  • Same flow for month, quarter and year.

If it is indeed like above, then I think you can just generate 4 sheets of weeks, months, quarters and years in advance, then relate the entries to the appropriate columns in those 4 sheets to display entries in inline list.

Thank you! Yes you are correct in regards to the user flow! In regards to creating 4 sheets for each time period in advance, is this a little bit like making a calendar table in a traditional database? However, there would be 4 calendar tables? One for each time period?

I am going to need to some some research and learn how to implement the solution you mentioned (e.g. Learn what Glide’s computed columns are and how to use them). But I really want to get this built, so I’ll dig in and see what I can do! Thank you for all of your help thus far and for really helping to think through this solution. I appreciate your time.

2 Likes

I don’t know how they do it in “traditional database” but with what Glide has now I think that approach is the easiest for you to start with.

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.