How to add a column that keeps track of count on two other columns

[mori]

I have a dataset containing Issue ID, Issue Status (Open, Close), Date Opened, Date Closed

Not all the issues have a Date Closed as some of them are still open. I need to make a graph that on the X axis shows the Time (Dates) and on the Y axis shows the net number of open issues. I want to prep the data in Alteryx before making the graph in QlikView. How can I add a column to my dataset that keeps track of number of open issues chronologically?

Here is a numerical example:

Issue ID    Issue Status    Date Open    Date Closed

1                Close               2/1/2016        3/4/2017

2                Close               4/6/2016        5/5/2017

3                Open                5/8/2016

4                Open                5/8/2016

According to the above example bellow would be the Net number of open issues:

Date          Net # of Open Issues

2/1/2016          1

4/6/2016          2

5/8/2016          4

3/4/2017          3

5/5/2017          2

[mori]

Would you please depict it visually so I know what you exactly mean?

[jdunkerley79]

 All I meant was:

Which produces this output:

This assumes the input has the dates in the input in Alteryx Date (or DateTime format)

Cleaned up workflow attached

[mori]

But the attached file does not show how you connected the results back to the original table so we can use the results along with all other columns. I am sorry maybe I am missing some points of yours. I am very new to Alteryx and your answers have been already of a lot of help.

Thanks!

[jdunkerley79]

Got you...

 

Couple of joins needed

- Join Date Open to Date

- Join Date Closed to Date

For the Date Open nothing will be lost for the Date Closed the NULLs need to then be Unioned back in

Example attached

[mori]

Seems we are closer but now we missed the column that would give the 'Net # of Open'.

[jdunkerley79]

Have gone back to basics. 

I changed the transpose to keep the Issue ID in process. This means the output at the end of running total looks like:

This makes it much easier to then join back and get a clean sequence of NetOpen increasing / decreasing. The output looks like this:

The last two columns show the net # of Open at both DateOpen and DateClosed

i suggest if this is still not quite the output you need you stick together the required output in Excel and attach

Workflow for above attached

[mori]

attached is how I need the output look like

[jdunkerley79 · Accepted solution]

Thanks much easier than second guessing...

So:

• Produce a table of Net open by Issues ID and dates first (as before)
• Join ID and Date Open (in the top join) and join ID and Date Closed (in the bottom Join)
• Union the two 
• sort by date and ID

Updated sample attached

[mori]

This is perfect! Thank you so much for helping me along the way! You are awesome!

[mori]

I used the real data and added two DateTime parsers to make the Date Open (in the real data the name is 'Created') and Date Closed follow the same format. However, I get the 'DateTime fields types can only be joined to their exact type.' error as I attached the screenshot.