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]

The problem is that the target date is comprised of two different columns: Date Closed and Date Open. I don’t know how to join it to both of these columns.

[jdunkerley79 · Accepted solution]

I suggest something like this:

1. Use a Transpose on Date Open and Date Close to create a list of dates
2. Filter the Nulls
3. Parse the dates (I have assumed US format)
4. Use the column name Date Open / Date Closed to produce a +1/-1 value
5. Sum these to get totals by date
6. Finally use a running total to get number open on a date

Sample attached

[mori]

Thank you so much for the reply!

I just have a couple of questions:

1- Why would you parse the dates if they are already in the date format?

2- Where did you define [Name] that you used in the "IIF([Name]="Date Close",-1,1)"?

3- In the summarize section there is an action box in which you GroupBy the Field named Value to an output filed Name of DateSTr. Why is that? I'd assume we only need to Sum the OpenClose by Grouping the Date.

4- My example was a simplified version of a dataset with many more columns that need to be used for the filtering purposes in the visualization. How do I add the OpenClose column to my dataset that does not affect the integrity of other data and columns?

I'd truly appreciate your answer.

[jdunkerley79]

I parsed the date from M/D/YY format to Alteryx standard YYYY-MM-DD format as this allows it to be easily sorted

The [Name] column comes out from Transpose tool consisting of Date Open or Date Closed (I had a typo in my version which meant I had Date Close - fixed version attached).

I kept the DateStr to get back the original unparsed date for simplicity. Indeed this is unneeded but seemed the easiest way to get to your wanted output.

Having produced this output set you can then join back to the original data set using the DateStr field that way the original input will not be significantly changed.

[mori]

I am afraid I am a bit confused! The fact that we transposed two columns makes a longer column. Thus, if we join it back to the original data it might not join well with the original table. Attached you can see the screenshot for the real file. I just don't know to which column of the original file should I join the DateStr.

[jdunkerley79]

Ah now I see.

I assumed you didn’t have the dates parsed into Alteryx format this means you don’t need the parse.

You can then just join the date in the summarise to the target date.

Hope that makes sense (not in front of Alteryx)

[jdunkerley79]

I would suggest joining on date open as this will give you number of tickets open on a date and I think that makes sense but depends on what you want to do with it.

[mori]

I actually did as you suggested. However, it eliminates all the info related to Date Closed. Meaning that you only see increase in the running total as it is joining only to the date open.

[jdunkerley79]

I think the join is just causing confusion.

The original output minus the datestr column is probably what you need.