590views

Start & End Date Buckets

#buckets

I have a date field and a variable field. See the example table below.

Date	Field
1/1/2018	A
1/2/2018	A
1/3/2018	B
1/4/2018	B
1/5/2018	A

I need a table (preferably cross table) that looks like this.

Start	End	Field
1/1/2018	1/2/2018	A
1/3/2018	1/4/2018	B
1/5/2018	1/5/2018	A

I'm just not sure on how to do this. Another challenge is that the original data is not sorted like the first table. I was able to accomplish this in Excel using a formula that created a count when the field value changes but that only works when the original data is sorted.

Thanks in advance.

Feb 07, 2018 - 1:54pm

dfeteau

Flag for Review

+ Add a Comment

(2) Answers

Pivot the data!

Insert > Data Transformation > Select your data table > Select pivot transformation > Add

Field will be your Row Identifier, Values will be(Min) date and Max(Date). You can change column names after you perform the pivot.

1 Comment

Your example is what I already have. In the example I gave, I have two "A" values with different start and end dates. What I'm trying to do is calculate the date changes for each field.

I'm able to find the breaks using a simple formula in Excel but it requires the table to be sorted so I can find the appropriate breaks (see the attached image).

Hope this explains it better.

dfeteau - Feb 09, 2018 - 8:37am

Flag for Review

+ Add a Comment

Attachment	Size
example.png	31.54 KB

Flag for Review

Feb 08, 2018 - 1:50pm

akrall-0

1 Comment

Your example is what I already have. In the example I gave, I have two "A" values with different start and end dates. What I'm trying to do is calculate the date changes for each field.

I'm able to find the breaks using a simple formula in Excel but it requires the table to be sorted so I can find the appropriate breaks (see the attached image).

Hope this explains it better.