8 Replies Latest reply on Jun 14, 2017 12:50 PM by Justin Litzner

    Charting two dates as one

    Justin Litzner

      Hello everyone,

       

      I am new to Tableau and still trying to learn the different lingo. I tried searching but may not know exactly what to search for. Anyways, I'm going to try and simplify this as much as possible and appreciate any help!

       

      I have a table with several different dates. One is basically an 'activity start date' and another is an 'activity ended date'. I have another field that is the 'case number'. And a fourth field which is an 'activity status' field. I want to count all of the 'case numbers' and graph them weekly by a running sum on 'activity start date. This will show me how many total cases we have and how many came in each week. I then want to find all of the cases that have an 'activity status' of "Completed" and graph this as a line chart weekly on 'activity ended date'. This will show me how many of those cases are completed and how many get completed each week. I can do all of this, but I want this to be one chart, so my question is how do I get these two different time periods (activity start date and activity ended date) to show up as one with the case #'s and completed cases.

       

      One case can have an 'activity start date' of Jan 1 and an 'activity ended date' of Jan 21. If a case is opened during the week of Jan 1, it will get added and show up as 'week of Jan 1' for the 'activity start date', so we will have a bar chart with 1 case for that week; and the line chart, which is completed, will be 0. Lets say this case was then completed during the week of Jan 21, so even though the 'activity start date' was the week of Jan 1, I want this to show up that it was completed during the week of Jan 21 (the date activity ended field), so we now have a total of 1 case for the week of Jan 21 (bar chart - running sum of Case #s) and 1 completed for the week of Jan 21 (line chart -  running sum of completed Case #s). 

       

      Right now I have a synced dual axis for the rows which included the case #'s and completed cases. I then have week of 'activity start date' as a column. I tried adding 'activity ended date' into the columns and creating a synced dual axis but this created two of each the case #'s and completed cases. 

       

      Not sure if I need to create a calculated field or if there is an easy way to combine those to get them to do what I want, so any thoughts would be helpful! Pretty sure I'm not allowed to share our data on here (once again - new - so not totally sure yet, but better safe than sorry), so this might have to be solved he old fashion way...

       

      Thanks in advance!

      Justin

      10.2

      Windows 7