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

# Counting the number of active projects by month or day

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!

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

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:

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.

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.

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.

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.

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

And lastly a chart that looks like this:

• ###### 2. Re: Counting the number of active projects by month or day

Thank you!!! It works!

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

Glad to have helped! Thx, Don