5 Replies Latest reply on Feb 27, 2019 3:16 PM by Don Wise

    Plotting the ongoing status overtime - a slightly more complicated (start date-end date)/(case open- case closed) question


      I've been researching like crazy the last day or so and I really wanted to find a solution to this without asking here, since the place is flooded with these kind of questions. But I just can't seem to find a way to solve this one, maybe my data needs to be restructured first.


      Unlike many of the issues related to this, I haven't got a column for the start and another for the end date. Instead I have two columns, one for 'Old Status' and 'New Status', then a third for the date in which the status change was made:


      This dataset is then merged with another that has more details on each requirement ID (for example RE-1000 might be implemented in 3 markets, China, Singapore, Taiwan...so in the other dataset there a 3 rows for RE-1000, one for each market.


      I've worked out the time that each requirement spends at each status (Time at Status). This is the key metric, how long are requirements spending at a status vs. the SLA. Each status can be put into a category, Status Type, (Delivery, Opportunities, Planning, Triage, Closed).


      Am trying to visualize both, the Status and Status Type, over the weeks of the year, and also the months. E.g. RE-1000 was created in October, passed through different statuses until February where it still remains open. So it would count as 1 requirement in 'Triage' for October, November, December, January and February. Once it moves to another status type this would be reflected in the month of that change. Likewise, for all the weeks during this time.


      At the minute I can only get a 'snapshot' for the date the change was made, if nothing happened to that requirement the following week, it drops off the viz until there is a status update again.


      Will I need to change the data structure so I have a 'Old Status Date'  and 'New Status Date'?


      Or can this be done using some custom SQL and a cross join like in the related threads?


      Any help or advice is greatly appreciated. I've attached the example workbook.