Group by to filter data

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 2020-05-20 09_31_59-HappyKids - Google Sheets et 5 pages de plus - Travail - Microsoft​ Edge

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?

@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.
1 Like

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

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.

@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?

@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.

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

1 Like

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

image

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.

image

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.

1 Like

that was, was I had in mind but same problem :frowning:
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

1 Like

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

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 !

@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.

3 Likes

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.

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

image

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

1 Like

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

2 Likes

I’m glad to hear we figured it out.

1 Like

That was absolutely amazing Nathanael. Hats off.

1 Like

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.

2 Likes