9 Replies Latest reply on Dec 18, 2017 10:40 AM by Matthew Theis

# Number of issue IDs opened and open by month

Attached is a sample data. What I want to achieve in Tableau is showing in one line graph the following:

1. number of issues that were opened in each month (newly opened)

2. number of issues that were in "open" status as of each month (eg. if as of 10/2017 there were 20 issues in "open" status. In 11/2017, 2 were opened and 5 were closed. Then as of 11/2017, 20+2-5=17 were in "Open" status

To further explain it, if there were 25 newly opened in Jan, then the total in "open" status would be 50 in Feb.

I'm new to Tableau, I only can graph #1 (see attached) and for #2, I think I need to use some calculated fields to count how many are in "open" and "closed" then do the math in Tableau. And finally somehow combine with #1?

• ###### 1. Re: Number of issue IDs opened and open by month

Hello Holly h  What have you tried so far?  Do you have a .twbx that you should share?

Thanks,

Matthew

1 of 1 people found this helpful
• ###### 2. Re: Number of issue IDs opened and open by month

Thanks Matthew. I added further explanation and a sample Tableau workbook.

1 of 1 people found this helpful
• ###### 3. Re: Number of issue IDs opened and open by month

Holly h  Check out Sheet4.  This was built using 10.4...Sorry.

Matthew

Issues Opened:    SUM(IF [Issue Status] = "Open" THEN 1 ELSE 0 END)

Issues Closed:    SUM(IF [Issue Status] = "Closed" THEN 1 ELSE 0 END)

Total Issues:        [Issues Opened]-[Issues Closed]

1 of 1 people found this helpful
• ###### 4. Re: Number of issue IDs opened and open by month

Thank you Matthew. Unfortunately I can't open your workbook because I'm using version 10.3.

I see the logic for the three calculated field, what if I have datasets from different reporting periods?

• ###### 5. Re: Number of issue IDs opened and open by month

Best,

Matthew

1 of 1 people found this helpful
• ###### 6. Re: Number of issue IDs opened and open by month

Hi Holly,

You 'should be able to open the attached.  It is Matthew Theis solution but I've changed it to v10.1.5 (the version I'm currently using) so please give all credit for the work to Matthew.

As a side not you can do this change yourself relatively easily.  All I do is rename the .twbx file to .zip.  Inside the zip file there is a file with extension .twb.

If you open this file in notepad and replace the third and fourth lines with the equivalent lines from one of your own workbooks the file will normally open so long as there are no features that your version of tableau does not support.  (i.e. replace the lines beginning

<!-- build

<workbook source-build=

Regards,

Paul

1 of 1 people found this helpful
• ###### 7. Re: Number of issue IDs opened and open by month

Good Stuff here Paul Cawford ! Thanks for sharing.

Matthew

1 of 1 people found this helpful
• ###### 8. Re: Number of issue IDs opened and open by month

Thanks again. This is helpful but the logic is not exactly correct because you got negative number of counts. I think it is more complicated than that.

1 of 1 people found this helpful
• ###### 9. Re: Number of issue IDs opened and open by month

Holly h   Based on the data you provided, it holds true.

1 of 1 people found this helpful