Filtering: Creating categories from start and end dates?

Phillipa · September 10, 2014 12:01PM

Hello All,

I have no experience of using date fields and need help.

Specifically, I have various types of chemotherapy that have a start date and an end date.
I need to be able to determine in which quarter a specific chemo took place. And really need separate fields for each quarter a bit like yes/no fields, as I split my data by quarters.

EG a chemo started on 01.02.2014 and ended on 14.09.2014 would be categorised in Q1, Q2 & Q3 but not in
Q4.

I have looked at buckets but can't seem to get my head around it.

Can anyone help please??

Thank you.
Phillipa

daniel · September 10, 2014 12:51PM

One thing you can do is create a series of new fields called Q1 - Q4 with formula conditions that state which quarter they were active in. Then create a tokenised field of all the Quarters which will allow you to select between them.

See the attached file and look at the formulas in each column.

Generally the formulas look like this:

IF
(
TEXTVALUE([Start Date] < DATEVALUE ("01-Apr-2014"))
+"_"+
TEXTVALUE([End Date] > DATEVALUE ("31-Dec-2013"))
="true_true","Q1",
""
)

The first condition is for the final date of that period and the second condition is the date just before the period.

I've used if functions and textvalues but you can actually use datediff and doing something similar.

The final field is just a concatenate field which you put in all the fields/periods you set up. I hope that helps!

Dan

Phillipa · September 17, 2014 6:17AM

Thanks Dan.

At the moment I in another "building site", but will try it out as soon as I have time.

Phillipa

Welcome!

Categories

Ideas Parade

Tagged