cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
micheleruggieri Regular Visitor
Regular Visitor

How to plot 2 date series in a single histogram

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 OpenCase Closed
2-Jan-181-Feb-18
9-Jan-188-Feb-18
16-Jan-1815-Feb-18
23-Jan-1822-Feb-18
30-Jan-181-Mar-18
6-Feb-188-Mar-18
13-Feb-1815-Mar-18
20-Feb-1822-Mar-18
27-Feb-1829-Mar-18
6-Mar-185-Apr-18
13-Mar-1812-Apr-18
20-Mar-1819-Apr-18
27-Mar-1826-Apr-18
3-Apr-183-May-18
10-Apr-1810-May-18
17-Apr-1817-May-18
24-Apr-1824-May-18
1-May-1831-May-18
4 REPLIES 4
Community Support Team
Community Support Team

Re: How to plot 2 date series in a single histogram

HI, @micheleruggieri

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

10.JPGBefore11.JPGAfter

Then close&Apply

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

 

Best Regards,
Lin

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
micheleruggieri Regular Visitor
Regular Visitor

Re: How to plot 2 date series in a single histogram

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?

lewysr Occasional Visitor
Occasional Visitor

Re: How to plot 2 date series in a single histogram

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.

Community Support Team
Community Support Team

Re: How to plot 2 date series in a single histogram

hi, @micheleruggieri

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

 

 

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 118 members 1,632 guests
Please welcome our newest community members: