Group by to filter data

[dharmel]

Hello,

Here is my app : https://nduqe.glideapp.io/
It aimed to list all kids activities in our region. However, every organizator has his own “ages range” and the filters is thus not really friendly.

Anyone has an idea how to solve that ? Is there a function to use in the googlesheet to improve that ?

Thanks !!

2020-05-20 09_31_35-Films et TV|250x500

[ThinhDinh]

Hi dharmel,

How would you want the filter to look like? A user selects a minimum age and a maximum age for their own, then all matching program appears?

[nathanaelb]

@dharmel What are you trying to achieve? What is the specific behavior you would like to see in your app?

A few ideas:

• The data in column C is aligning left and right. Unless you did this manually, this makes me think some of the cells are formatted to text and others to number. It’s good practice to have clean data and you could set a data type to the column and clean up the data.
• Depending on what you are trying to achieve, you might want to break up the information in column C: minimum age (one column), maximum age (another column). This is because in each cell in column C, you actually have two pieces of information.
• You can use the unique() function to create categories of your items.
• You can use the filter() function to create smaller data sets of your items. I see from your screenshot that you set a filter. The filter() function does the same, as a function.
• You can use a template column in Glide’s data editor or the concatenate() function in GS to then string your data together and create something nice like “8 - 18 ans”.
• If you do decide to break up column C (again it depends on what you are seeking to achieve), you might consider using the right(), left(), find() and len() functions to save time. Or maybe the split() function by using a space as the delimiter.

[dharmel]

Hello @nathanaelb ,
thanks for quick response !
Each organizator gives his range of ages (e.g : 4-7 years, 2-5 years, 2.5 - 4 years…)
What I would like to appear in the filter is 4 years, 5 years,…
Depending on that, it will select all activities where the age is included…
How do think I should proceed ?
Rgds

[nathanaelb]

Hi @dharmel

I would do what I suggested:

• split the string of text that your organizers submit thereby creating a range for each organizer with a min and max value (“4-7 ans” becomes min=4 and max=7)
• then in your filter or visibility settings, set your property “Age” to be greater than min and smaller than max.

These are just ideas and I would adopt a trial and error approach.

[dharmel]

@nathanaelb

Not sure to understand how to split it in my googlesheet… how do you define a range? I tried with a string “4;7” in one column or 2 columns, one with min and one with max. Then I defined a filter based with condition on min/max. But how it helps displaying only min and max ages of all activities.

Did I missed something?

[nathanaelb]

@dharmel
I’ll have a look this weekend, I cannot before, my apologies.

Another idea: in the search bar on the homepage of the forum, search for “filter on a range” or “filter on intervals”. Your use case might already have been discussed in another topic.

[ThinhDinh]

I will try to take time today to get some random data and explain the idea.

[ThinhDinh]

This is the way to take the min and max age values from the input.

Formula in column C:

={"Min age";ARRAYFORMULA(IF(B2:B<>"",VALUE(SUBSTITUTE(LEFT(B2:B,FIND("-",B2:B)-2),",",".")),""))}

Formula in column D:

={"Max age";ARRAYFORMULA(IF(B2:B<>"",VALUE(SUBSTITUTE(TRIM((SUBSTITUTE(RIGHT(B2:B,LEN(B2:B)-1-FIND("-",B2:B)+1)," ans",""))),",",".")),""))}

However, I encountered a problem with the logic when working with filter comparisons. As of now, we can’t directly compare a value to another column’s value.

I wanted this logic to be Child age (User’s specific input) to be larger than the min age and smaller than the max age, for example, but it was not achieveable.

Hope someone can chime in with another workaround based on the values taken from the age range.

[dharmel]

that was, was I had in mind but same problem
I was thinking using inline and make a tab with ages and then make relations with activities but then the display of activities’info in cards is not really good

[dharmel]

@nathanaelb don’t have any magical function (as the one for geolocalisation) ?

My problem : I have a column with ranges of ages for activities (differents for each organizator)
What I want : Display single value in the tab filter (2years, 3 years,…) and it matches all activities

Thanks in advance for your help !

[nathanaelb]

@dharmel I gave your problem some thought. Here are a few ideas. This post is long.

Before reading any further, you might want to watch Glide’s two following tutorials:
Array columns
Multiple categories

How your organizers are to input their offering

• I would force them to input a min. age and a max. age, which would define their offering.
• They can input for instance “4” and “8”. You can make it pretty on the front end by confirming that they are offering “4 à 8 ans” (by using a template column in the Glide data editor)
• This is to guarantee that your data is clean and it will save a lot of headache with cleaning the data in GS. Some will write “De 4 à 8 ans”, others “4-8 ans”, others “4 - 8 ans”, some will write “14.5 ans”, other “14,5 ans”. This is a headache.
• Here is a screenshot:
  
  
• “Organizer”: user input
• Min age: user input
• Max age: user input
• Category offering: template column in the Glide data editor. You can display this to your users to make it user friendly. I did this in GS, but it is a better practice to do it in the Glide data editor. In GS, here is the formula I created in row 2: =B2&" à “&C2&” ans".

Categorize your organizers by age category

• I would suggest creating age categories: we can then display and filter on these categories.
• Let’s assume the ages of the children range from 1 to 18: you will then define 18 age categories, which you can call “Catégorie âge 1”, “Catégorie âge 2”, etc.
• You might want to avoid special characters such as é or â in your headers, let’s keep it safe.
• It is important that when you name your categories, the numbers start with 1 and go up 1 by 1.
• You can create these columns in your worksheet.
• Here is a screenshot:
  
  
• The values in columns E (“Age Category 1”) to V (“Age Category 18”) are populated automatically once the min. age and max. age are inputted by the organizer.
• The GS formula in E2 is: =sequence(1,C2-B2+1,B2,1).
• I do not know how to wrap the sequence() function inside an arrayformula() function.
• Edit: I don’t know how to pull sequence() down the column. Maybe someone else can help here. This part is important: this would then guarantee that each new organizer is automatically assigned to age categories. Maybe you do not need this to be done automatically in real-time.

Display the organizers and then filter by age category

• In the Glide data editor, in your organizers’ worksheet, create an array column of columns 1 through 18. You can call it “Age categories” (for instance).
• Here is the Glide tutorial on array columns, and here the Glide tutorial on multiple categories. Both are helpful to understand categorizing your organizers by age category.
• In the Glide layout editor, on your organizers tab, if you allow users to filter with the “in-app filter” feature, your users should now be able to select the ages they want to see and only the organizers that fit that age category will display.

You will probably need to continue to test layout options to find something you like. I have the feeling that creating age categories and fitting organizers within these age categories might now allow you to filter your organizers by age offering.

[ThinhDinh]

@nathanaelb

I do not know how to wrap the sequence() function inside an arrayformula() function.

I don’t know if Sequence can be wrapped inside an Arrayformula, I don’t think it can, but I have written many scripts as a workaround, to copy down the formula from the row above when a new row is initiated. I will have a post about that as a point of reference for future use.

[nathanaelb]

@ThinhDinh

I don’t know if Sequence can be wrapped inside an Arrayformula

You can wrap arrayformula() around sequence() to create decimal numbers within the sequence, since the sequence() function does not allow decimal counts:

What I don’t know how to do is pull sequence() down the column.

[dharmel]

thanks a lot everyone ! It works perfectly with the secund option (1 column per age) and applying the arrayformula. You save me !

[nathanaelb]

I’m glad to hear we figured it out.

[ThinhDinh]

That was absolutely amazing Nathanael. Hats off.

[Gabriel_Sobral]

@ThinhDinh

I don’t know if Sequence can be wrapped inside an Arrayformula, I don’t think it can, but I have written many scripts as a workaround, to copy down the formula from the row above when a new row is initiated. I will have a post about that as a point of reference for future use.

I also have a Python script that rolls-down formulas that can’t be wrapped inside an ArrayFormula.
It is so useful!

But maybe for that application I think Google App Script is a more reasonable, simpler way of doing that.

[StephM]

Hi guys!

I am trying to add a second unit (“months”) to this age filter (currently only uses “years”) AND, if possible, I would also like to introduce ranges. Any idea how I could do this?

In an ideal world, I would like my filter to look like this:
0-6 months
6-12 months
12-18 months
18-24 months
2-3 years
3-4 years
4-5 years
5-6 years
6-8 years
8-12 years
12 years+

In a less than ideal world, it could look like this:
6 months
12 months
18 months
24 months
3 years
4 years
5 years
…
12 years

What would you recommend?

Thanks so much!!

[nathanaelb]

Hi @StephM, how are you?

I believe you can achieve your “ideal world” case. Start with the end user experience in mind (the filter by age category), organize your data accordingly (your spreadsheets, your columns, the data types), and then set up the data input according (add/edit components and data types).

• (Step A) The end goal is your filter. Aim to have your “Age category n” columns side by side and populated with the elements of your “ideal world filter”. Doing it by hand is your first step. Do this to make sure you get it to work in the front end. Get the UX right first before you spend time fiddling around with tables and data and formulas.
• (Step B) Then, see how your users are submitting the data. Set this up and do it in such a way that once in GS or the GDE (Glide Data Editor), the data is going to be somewhat easy to exploit.
• (Final step) You need to take the data from Step B, and transform that data so as to achieve the result in Step A. This might require a little “spreadsheet magic”, or creating columns and moving data around via the GDE (preferred method).
• That’s it.

Make sure you meditate this post above. Watch the videos. The process is broken down into the different steps.

I hope this helps.