CODEWITHSUNDEEP
tableau-api
radicand
radicand

Reputation: 6638

Tableau month-based bar chart for data with date range

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.

All I can do right now

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?

Upvotes: 1

Views: 2191

Answers (1)

Prem
Prem

Reputation: 11965

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!

Upvotes: 1

Related Questions