How to plot 2 date series in a single histogram

[Anonymous]

Hello,

 I was hoping you could help with the following. I have 2 columns called "Case Open" and "Case Closed" showing when each case was open and closed. I'd like to plot these 2 date series in a single graph using histogram, i.e. an histogram with Months as X-axis, and 2 separate columns showing how many open and closed cases I had in a particular month. I don't know why but if I simply use the histogram function no matter what I do, always the same numbers show for open and closed cases for each month, i.e. the two columns for each month are identical even tough the data is different for open & close, like below.

Thanks a lot in advance!

Case Open	Case Closed
2-Jan-18	1-Feb-18
9-Jan-18	8-Feb-18
16-Jan-18	15-Feb-18
23-Jan-18	22-Feb-18
30-Jan-18	1-Mar-18
6-Feb-18	8-Mar-18
13-Feb-18	15-Mar-18
20-Feb-18	22-Mar-18
27-Feb-18	29-Mar-18
6-Mar-18	5-Apr-18
13-Mar-18	12-Apr-18
20-Mar-18	19-Apr-18
27-Mar-18	26-Apr-18
3-Apr-18	3-May-18
10-Apr-18	10-May-18
17-Apr-18	17-May-18
24-Apr-18	24-May-18
1-May-18	31-May-18

[v-lili6-msft]

HI, @Anonymous

After my test, you may try this way as below:

In Edit Queries, select Case Open column and Case Closed column then click Transform->Unpivot columns

BeforeAfter

Then close&Apply

and darg new field value as X-axis, and Attribute field as legend

Best Regards,
Lin

[Anonymous]

Thanks! Just what I was loking for! 

One minoe issue with my histogram is that each attribute on the histogram either starts late or finihses early, 

ie Case Closed doesn't start from 0 from the earliest date (which is 1/2/2018 Case Open in example above).

Same for Case Open, this stops at the latest Case Open Date as opposed to the latest date in the histogram.

Is the a simple solution ofr this that I am missing?

Apologies for jumping on the thread.

[Anonymous]

Thank you. I'm not sure your solution would work in my case since my table is made up of 30+ columns with thousands of rows, one row each case.

I have several other visuals already setup that read from the other colums. With your solution I would effectively double the number of rows just for 1 column, and although the histogram would work, I would think all the other visuals will stop function the way they should?

[v-lili6-msft]

hi, @Anonymous

For Case Open and Case Closed are two columns and put them together. it is bound to twice the size of the dataset.

and you could do these for the histogram visual and other visuals

Step1:

In the Edit Queries, duplicate basic table and then Unpivot columns for the duplicate table

Step2:

Create a relationship between the basic table with duplicate table ( optional )

Step3:

Use duplicate table only to create the histogram visual and basic table to create other visuals.

Best Regards,
Lin