Start & End Date 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.
(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.
| Attachment | Size |
|---|---|
 example.png | 31.54 KB |
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.
You may want to create two calculated columns:
Start Date : Min([Date]) over Field
End Date : Max([Date]) over Field
1 Comment
I tried that before coming to the forum. That'll give me the min and max but not divide them correctly.