Tableau month-based bar chart for data with date range

Question

I have data similar to the below:

id | start      | end        | name
1  | 2017-01-15 | 2017-03-30 | Item 1
2  | 2017-02-01 | 2017-05-15 | Item 2
3  | 2017-02-15 | 2017-04-01 | Item 3

I want to represent this as a bar chart with Month on the horizontal axis, and count on the vertical axis, where the value is computed by how many items fall within that month. In the above data set, January would have a value of 1, February would have a value of 3, March would have a value of 3, April would have a value of 2, and May would have a value of 1.

The closest I can get right now is to represent the count of items with the start or end date, but I want the month to represent how many items fall within that month.

I haven't found a way to do this in Tableau without restructuring my data set to have each current row restated for each month, which I don't have the luxury to do. Is this possible at all?

Answer 1

One solution could be to have 12 calculated fields like below

id | start      | end        | name   | Jan | Feb | Mar | Apr | May...
1  | 2017-01-15 | 2017-03-30 | Item 1 | 1   | 1   | 1   | 0   | 0
2  | 2017-02-01 | 2017-05-15 | Item 2 | 0   | 1   | 1   | 1   | 1
3  | 2017-02-15 | 2017-04-01 | Item 3 | 0   | 1   | 1   | 1   | 0

Definition of calculated fields -

• 'Jan' is DATENAME('month',[start]) <= 1 & 1 <= DATENAME('month',[end])
• 'Feb' is DATENAME('month',[start]) <= 2 & 2 <= DATENAME('month',[end]) and so on...

Then using Pivot option in Tableau, convert it to something like

name  | Month | Count
Item1 | Jan   | 1
Item2 | Jan   | 0
Item3 | Jan   | 0
...
Item1 | Feb   | 1
Item2 | Feb   | 1
Item3 | Feb   | 1
...
Item1 | Mar   | 1
Item2 | Mar   | 1
Item3 | Mar   | 1
...

Drag Month to 'Columns' and SUM(Count) to 'Rows' to generate the final visualization.

Hope this helps!