4 Replies Latest reply on Feb 13, 2019 5:52 AM by André Brasil

    Time graph: running sum of active courses only

    André Brasil

      Hi,

       

      I'm having a lot of trouble to get a graph to work the way I need it to, and I hope someone can help me with that.

      The object of the analysis are courses in two modalities: M and D, which can be in PROJECT, OPEN, CLOSED or CLOSING. What I need to do is to create a graph, year by year, counting the number of courses which were operating on that year. My data looks something like:

       

      CodeStartDateEndDateStatusTodayLevel
      41001010001M601/01/1976-OPENM
      41001010001D701/01/2013-OPEND
      41001010004D601/01/1998-OPEND
      41001010004M501/01/1975-OPENM
      41001010005D201/01/1987-OPEND
      41001010005M101/01/1971-OPENM
      41001010006M801/01/1969-OPENM
      41001010006D901/01/1981-OPEND
      41001010007M401/01/196923/05/2002CLOSEDM
      41001010007D501/01/198923/05/2002CLOSEDD
      41001010008D101/01/2000-OPEND
      41001010008M001/01/1971-OPENM
      41001010009D801/01/1993-OPEND
      41001010009M701/01/1976-OPENM

       

      Considering the start and end dates, I would need to plot the number of courses which are active every year, like this:

       

      Screenshot 2019-02-13 at 11.32.34.png

       

      But my problem is that the graph ends up showing all of the records according to the filter and what I want is to have the number of OPEN courses, so a course opened in 1969, but closed in 2002 would be counted until 2002 and not after that.

       

      I know there are some ways to do that by creating another table calculating the number of active courses at any given year, and them plotting that, but my whole database is 8000 records long, with lots of geographical possibilities and subcategories which I'll need to explore later, so I wonder if anyone knows how to do what I need in a dynamic way.

       

      Thanks,

        • 1. Re: Time graph: running sum of active courses only
          sajad.bhat

          Hi Andre,

           

          Have attached workbook with workaround, let me know if it helps, in that case you can mark it as answered/helpful. otherwise you can give some more insights.

          • 2. Re: Time graph: running sum of active courses only
            Zhouyi Zhang

            Hi, Andre

             

            I know you may know this workaround already, but I think the best practice is to join your data to a table with all year listed as shown below

             

            Hope this helps

             

            ZZ

            • 3. Re: Time graph: running sum of active courses only
              André Brasil

              Hi, Sajad,

               

              Thanks for your help.

              I was checking your file to see if everything was ok, as well as to try and understand your methodology, but I noticed the acumulated history was not quite right. As you can see on the table below (only D courses), the first one was created in 81 (1), then another in 84 (2), two more in 87 (4), two in 88 (6), one in 89 (7), and so on.

              Screenshot 2019-02-13 at 14.28.53.png

              But you see that the field generated to count active courses did not count the 1989 course which was closed in 2002 (since the StatusToday is fixed, showing only how is the status of the course today). What I needed was this 1989 course to count until 2002, and then not count starting in 2003. With that, the results ended up being similar to just filtering out courses that are no longer OPEN.

               

              But thanks anyway. I will try to explore some workaround using your approach.

               

              André

              • 4. Re: Time graph: running sum of active courses only
                André Brasil

                Hi, Zhouyi,

                 

                Thanks for the tip.

                This would be actualy easier and more dynamic from the approach I would do with a second table (which would require counting active courses per year and exporting results and importing the table back to Tableau) but would also generate a problem for me. Since I'm working with a 7000 line table (with over 100 columns of distinct data about the courses and some other tables to associate to them), I would end up multiplying my database by the number of years in the analysis (since 1931, in the full set).

                 

                Anyway, if I can't find the result I'm hoping for, at least I can create a secondary dataset, with only selected columns, to get the job done.

                 

                Thanks,

                 

                André