Yes, I've already found this FAQ, but I wasn't able to find one that helped. I have been looking at the Cross Join Collection link most, and I thought that method #1 should work for this issue, but unsure how it can be applied to my dataset.
1 of 1 people found this helpful
I took a look for you on this...I used a technique called 'scaffolding' which bridges your existing dates with another set of separate but continuous dates. In the attached 10.2 workbook, i also created two new dates, a start date and end date based on the min and max date/time values by ID. So for each ID there's a unique start and end date/time. The continuous dates are then used to compare what activity (status type) occurred in between those dates.
There's a boolean (True | False) filter which essentially counts what the status is for each ID for whatever the date is truncated down to, in your case by week. There's an explanatory thread here on technique: Identification of Active Customers in a Given Period
This is the initial worksheet. What I gathered from your post is that you wanted to see the trended lines across all weeks (months) etc. Based on the data points displaying where they are, each status doesn't have its own start/end dates, as you noted.
I also tried setting your date to Continuous, which works, but you'd lose some date formatting functionality in the bottom axis because of the change to continuous.
But that wouldn't have resolved the inherent problem which is each status having its own start and end dates and getting appropriate counts within those periods.
This is the result with date scaffolding. The filter is counting each ID found between the new start and end dates broken down by status by week.
In validating the underlying counts against the initial results in the first screenshot, for Week 31 (start of axis), there is a count of 6 status = Triage, whereas with date scaffolding there is a count of 5 status = Triage. In looking at the underlying data AND because the week is truncated to a week by week view, I found that the initial method is counting ID-363, whereas date scaffolding is not. In looking at the row involving ID-363 it was open and closed the same day, therefore not counted in the scaffolding method for the week. You'll likely need to do a deeper dive into your data to validate whether the scaffolding is producing the right values for you but historically from what I've seen with other efforts, I think they probably are.
Hope it helps! Thx, Don
I'd already started working on something similar when you replied, but was struggling, I used what you did here as a handrail and made some progress. In the end I thought it was less of a headache to just go back and create a Time in/Time out column, it just made things a little easier when it came to trickier visuals. But this helped a lot still, thanks! And sorry for the late reply!
I'm just glad I was able to support you in some manner! Glad it worked it out for you!!