3 Replies Latest reply on Nov 6, 2018 1:42 PM by Don Wise

    Counting the number of active projects by month or day

    Kevin Clark

      I found a solution in Excel to count number of active projects.  I was wondering, can this be created in Tableau using calculated fields?

       

      Thanks!

       

      enter image description here

       

        • 1. Re: Counting the number of active projects by month or day
          Don Wise

          Hi Kevin,

          There are a number of posts on this, but to help you along I've attached a sample workbook using your data from the screenshot above to help get you started.  The attached is 2018.3 version .twbx  Below are the screenshots of the various steps and calculations taken to get to the data and chart. If it helps please mark this response as helpful or if correct mark as correct so that others may find it useful in the future.  Thx!  Don

           

          Your data example brought into Tableau:

          Screen Shot 2018-11-06 at 10.45.06 AM.png

          In the MS-Excel data I added a new worksheet and dragged that in to perform what's known as a Cartesian join.  The worksheet is nothing but a series of sequential dates that act as a 'scaffold' to your data.  This is a required step in order to get to the view you desire.  You'll have to bring in a fact table of nothing more than a series of sequential dates for the time frame involved, which in your case may be a short period as shown, or in other cases may involve years.  I'd suggest trying to keep this at relatively small periods for best success.

          Screen Shot 2018-11-06 at 10.45.12 AM.png

          Once the date fact table is joined to your data, Tableau will want (notice the exclamation mark) a better join to work with.  In this particular case, to perform the scaffolding and Cartesian join, use the drop-down and select Edit Join calculation. A dialog box will open up thereafter.

          Screen Shot 2018-11-06 at 10.45.23 AM.png

          Type a 1 then click Apply and OK.  Do the same for the other side of the join, Type a 1 and then click Apply and OK.  Ensure you're using an INNER JOIN.

          Screen Shot 2018-11-06 at 10.54.21 AM.png

          This calculation creates a boolean (true/false) condition which is then used as a filter. Drag to Filters.  Drag the other dimensions as shown to either Columns/Rows.

          Screen Shot 2018-11-06 at 10.55.28 AM.png

          You'll end up with a table of data that looks like this:

          Screen Shot 2018-11-06 at 11.05.22 AM.png

          And lastly a chart that looks like this:

          Screen Shot 2018-11-06 at 11.05.25 AM.png

          • 3. Re: Counting the number of active projects by month or day
            Don Wise

            Glad to have helped! Thx, Don