Helpful Calculated Fields in Casebook

Casebook allows you to define new fields in the dataset, named calculated fields. The following fields can be copy and pasted into Casebook.

Length of First Name

find(" ", [person_name], 1)

Notes:

• This is assuming you don't have a middle name. If you do, it will get wrapped into the last name. This is assuming you don't have last names longer than 20 characters.
• If you do, change the last argument of last name to be more than 20.
• This is assuming your first and last name is separated by a space (ie. there's no space in your first name).

First Name

mid([person_name], 1, [Length of First Name])

Notes:

• This is assuming you don't have a middle name. If you do, it will get wrapped into the last name. This is assuming you don't have last names longer than 20 characters.
• If you do, change the last argument of last name to be more than 20.
• This is assuming your first and last name is separated by a space (ie. there's no space in your first name).

Last Name

mid([person_name], [Length of First Name], 20)

Notes:

• This is assuming you don't have a middle name. If you do, it will get wrapped into the last name. This is assuming you don't have last names longer than 20 characters.
• If you do, change the last argument of last name to be more than 20.
• This is assuming your first and last name is separated by a space (ie. there's no space in your first name).

Find Cases Without a Person

if(isempty([person_id]), “Missing Data”, “Data is entered”)

Notes: 

• Starting from the Cases Dataset

Find People Without a Case

if(isempty([case_id]), “Missing Data”, “Data is entered”)

Notes: 

• Starting from the People Dataset

Finding Children

if(age < 18, "Under 18", "Over 18")

Notes: 

• Using the People Dataset

Age Range

IF(isempty([age]),"Not Reported", IF([age]<=3, "0-3", IF([age]<=5,"4-5", IF([age]<=9,"6-9", IF([age]<=18,"10-18",IF([age]<=29,"19-29",IF([age]<=39,"30-39",IF([age]<=49,"40-49",IF([age]<=59,"50-59",IF([age]<=69,"60-69",IF([age]<=79,"70-79","80+")))))))))))

• The age ranges in the formula can be altered to your specific needs.

Gender Identity 

IF([gender_identity] = "Woman (girl, if child)", "Female", IF([gender_identity] = "Man (boy, if child)", "Male",  IF(OR([gender_identity] = "Transgender",  [gender_identity] = "Questioning",   [gender_identity] = "Culturally specific",   [gender_identity] = "Different identity",   [gender_identity] = "Doesn't know"), "Other", IF(OR([gender_identity] = "Prefers not to answer",  [gender_identity] = "Undisclosed",   [gender_identity] = "Unknown"), "Undisclosed",          "Other"))))

Notes:

• Using people or gender_identity datasets 

Point-in-Time Calculated Fields

Scenario 1: Started in the Reporting Period

AND([Start Date]>=[Reporting Period Start],[Start Date]<=[Reporting Period Start])

Notes: 

• Start Date and Reporting Period Start need to be defined.

Scenario 2: Ended in Reporting Period

AND([End Date]>=[Reporting Period Start],[End Date]<=[Reporting Period End])

Notes: 

• End Date, Reporting Period End and Reporting Period Start need to be defined.

Scenario 3: Started before the beginning of the Reporting Period & Ended after the reporting period

AND([End Date]<[Reporting Period Start],[Start Date]>[Reporting Period End])

Notes: 

• Start Date, End Date, Reporting Period End and Reporting Period Start need to be defined.

Scenario 4: Started before the beginning of the Reporting Period, No End Date

AND(ISEMPTY([Start Date]),[End Date]<=[Reporting Period Start])

Notes: 

• Start Date, End Date, and Reporting Period Start need to be defined.

Combining Scenarios

IF(OR([Scenario 1],[Scenario 2]),"In Reporting Period", "NOT in Reporting Period")

Notes: 

• You can combine as many scenarios as needed.
• Scenario 1, Scenario 2, etc, need to be replaced with the above formulas. 

Example of ALL Scenarios Above

IF(OR(AND([Start Date]>=[Reporting Period Start],[Start Date]<=[Reporting Period End]),AND([End Date]>=[Reporting Period Start],[End Date]<=[Reporting Period End]),AND([Start Date]<[Reporting Period Start],[End Date]>[Reporting Period End]),AND(ISEMPTY([End Date]),[Start Date]<=[Reporting Period Start])),"In Reporting Period","NOT in Reporting Period")