Calculating the Range of Age Groups

[BCM]

Hello,

I have 2 columns Date_of_Birth and Age_Group. I have to calculate age group in a range from given date of birth as mentioned below:

If the age falls between 0 month to 1 Year Age Group should be "0-1 Yrs" and so on. "1-2 Yrs", "2-5Yrs", "5-6 Yrs", "6-10 Yrs", "10-19 Yrs", "19-49 Yrs", "49-60 Yrs" and "60+ Yrs", further data entry is based on Show_If(age_Group="19-49 yrs") and so on.

Kindly guide me achieve this.

Thank & Regards

[Marc_Dillon]

You would need to utilize a large IFS expression.

https://help.appsheet.com/en/articles/2355961-ifs

[Suvrutt_Gurjar]

Sorry Marc. I had not seen you had already replied. Yes, as you mentioned, it is a large IFS() expression.

[Joseph_Seddik]

And I haven't seen yours 🙂

[Suvrutt_Gurjar]

---

@Joseph_Seddik wrote:

And I haven't seen yours 

---

No worries 👍 . More solutions/ options are always good to have. 🙂

[Suvrutt_Gurjar · Accepted solution]

Please try an expression something like below

IFS(
[Date_of_Birth]>TODAY(), "Invalid date",
EOMONTH([Date_of_Birth],11)+DAY([Date_of_Birth])>=TODAY(), "0-1 Yrs" ,
EOMONTH([Date_of_Birth],23)+DAY([Date_of_Birth])>=TODAY(), "1-2 Yrs",
EOMONTH([Date_of_Birth],59)+DAY([Date_of_Birth])>=TODAY(), "2-5 Yrs",
EOMONTH([Date_of_Birth],71)+DAY([Date_of_Birth])>=TODAY(), "5-6 Yrs",
EOMONTH([Date_of_Birth],119)+DAY([Date_of_Birth])>=TODAY(), "6-10 Yrs",
EOMONTH([Date_of_Birth],227)+DAY([Date_of_Birth])>=TODAY(),"10-19 Yrs",
EOMONTH([Date_of_Birth],587)+DAY([Date_of_Birth])>=TODAY(),"19-49 Yrs",
EOMONTH([Date_of_Birth],719)+DAY([Date_of_Birth])>=TODAY(),"49-60 Yrs",
TRUE, "60+ Yrs"
)

Please test well, especially for edge cases. You can use this column further in your dependent calculations.

EOMONTH() - AppSheet Help

[Joseph_Seddik]

I'd do the following:

1. Create a two-column table, name it ageGroups.
   • Label column:  contains the age groups "0-1 Yrs", "1-2 Yrs", etc.
   • Key column: contains the highest age in days in each group, so for example, respectively: 365, 730, etc.
        
2. In your original table:
   • Add a new virtual column that contains the age in days, name it say ageDays. It's app formula will be: 
        
       TODAY() - [birthDate]
       
     You might rather want to calculate the age at a specific moment, for example at the time of subscription to a service or enrolment in a class, etc. rather than on a daily basis. In that case, you can use a normal column with the app formula:
       
       [subcriptionDate] - [birthDate]
       
   • Your Age_Group column now should be of type Ref pointing to the ageGroups table, with the following app formula:
       
       MIN( FILTER("ageGroups", [ageGroupKey] >= [_ThisRow].[ageDays]) )

[WillowMobileSys]

Yes, I agree, a lookup table is the way to go - for two reasons

1)  You are using this in multiple places

2)  With a table, should you decide to adjust your age-groups (happens all the time), then you only need to change the table - not the many places in the app.

CAUTION:  Considering 2) above, I would NOT use the "age days" as the Key.  Instead use a dedicated key column and set the age days as just a data column.  You'll need to adjust the expression slightly but functionally it will still be the same.  This helps should you ever need to adjust the age groups and need to re-classify rows - far fewer rows to change.

[Suvrutt_Gurjar]

---

@WillowMobileSys wrote:

1)  You are using this in multiple places

---

@WillowMobileSys : Maybe I am missing some details. Could you update why you felt this is being used in multiple places. I did not find that being mentioned in original post. By multiple places do you perhaps mean in each row of the table?

[WillowMobileSys]

---

@BCM wrote:

further data entry is based on Show_If(age_Group="19-49 yrs") and so on.

---

This comment implies that Age Grouping implementation is needed on the Show_If as well as the usual classification on the row.  While I understand that you can set a single value on the row and then reference it, it indicates the logic is needed in multiple places.  Maybe that's all but based on my experiences with this type of app, one that classifies on age groups, it's likely this same logic will be needed in many different places as the app expands - especially when implementing the reporting that normally goes along with this type of app.  

Regardless, when you fold in the likely need to adjust age group durations, the ability to adjust a table rather than needing to re-deploy the app is a big benefit.

[Joseph_Seddik]

I'd like to add that employing lookup tables is a widely used practice in programming in general not just in databases. In low-level programming a lot of DateTime and Math functions for example are deployed as lookup tables to save processing power. 

[Suvrutt_Gurjar]

Thank you very @WillowMobileSys  and @Joseph_Seddik  for valuable insights. I will definitely keep it at the back of my mind your thought process.

I understood you mentioned likely need of adjustment in age group and use at multiple places as salient points in favor of lookup table. 

[Joseph_Seddik]

@Suvrutt_Gurjar Welcome my friend ! The thanks are due to yourself for the numerous things I learned from your posts over the years..  

For the matter at hand, I'd say that a Read is always better than a Calculate. For this an important step that is unfortunately overlooked by too many developers is to spend time thinking about the most efficient data modelling before taking any step in app creation. I've seen many apps for example having serious performance issues that cannot be improved in any way from within the app due to flawed data modelling and inefficient table schemas. 

Also as far as AppSheet or a database in general is concerned, dividing complex calculations among several additional columns to read a value afterwards through a simple dereference expression, is better than having a large complex expression in just one column. 

[Suvrutt_Gurjar]

Thank you Josef for good words for me and more practical insights. Those are useful. 

---

@Joseph_Seddik wrote:

is better than having a large complex expression in just one column. 

---

I am aware you have given this as a useful general guideline. However, in this particular case of the current post, I may humbly say that it is a rather long IFS() expression with multiple similar conditions than a complex expression. It is using values from just the current row. 

Coming back to our current requirement

-------------------------------------------------------

@Joseph_Seddik wrote:

Your Age_Group column now should be of type Ref pointing to the ageGroups table, with the following app formula:
  
  MIN( FILTER("ageGroups", [ageGroupKey] >= [_ThisRow].[ageDays]) )

---------------------------------------------------------------------------------------------------

May I know what kind of column you have proposed for the [Age_Group] - physical or virtual? Is it virtual?

[Joseph_Seddik]

---

@WillowMobileSys wrote:

I would NOT use the "age days" as the Key

---

I agree. It is just a very small dataset so I wanted to make the smallest expression in this case, but generally you are totally right!

[Joseph_Seddik]

---

@Suvrutt_Gurjar wrote:

rather long IFS() expression with multiple similar conditions than a complex expression

---

I frequently use long IFS() expressions in virtual columns running on thousands of rows with absolutely no issue. What matters is the expressions inside. I agree yours is not complex. It was rather a general guidance, not related at all to this expression. Thank you for the added clarification!

---

@Suvrutt_Gurjar wrote:

what kind of column you have proposed for the [Age_Group] - physical or virtual? Is it virtual?

---

Virtual with no doubt! I propose expressions always for the case at hand, and here a very small dataset, few dozen lines, or even a couple of hundreds or so, it won’t be an issue.