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]<=12, "0-12", if(and([age]>=13, [age]<=17), "13-17", If(and([age]>=18, [age]<=24), "18-24", if(and([age]>=25, [age]<=59), "25-59", if([age]>=60, "60+", " ")))))))
Notes:
- The age ranges in the formula can be altered to your specific needs.
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")