This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
My objective is to create a report where it shows how many people are active at a specific point in time. Please see below for what I want to turn the data into. This would account each day looking at a span of a record to see if they fit between that record and if they do then it would count that individual toward that day. Instead of creating numerous workflows I am looking for some type of loop to do this for me.
Raw data Unique IdStart DateEnd Date 234 20140101 99991231 456 20151001 20161130 789 20151001 20161031 123 20160901 99991231 567 20160901 20161215 345 20161101 20161231 678 20161201 99991231
Report Jan 1 2014 … Oct 1 2015 … Sept 1 2016 …. November 1 2016 …. December 16 2016 1 3 3 4 4
The concept behind doing something like this is to get it to work in a workflow for one instance, and then you can expand that with your "loop" through a batch macro (where you will have a list of dates that you send through the same process).
The basics behind the workflow would be to have a Formula where you create a "flag" field that evaluates whether a given date is between your dates (which BTW, would need to be converted to a true Date format).
After that you merely have a Summary tool and count the non-null values in the flag field. So now you have a date with a count.
You then turn it into a Batch macro with the Control Parameter and Action, where the Action replaces the date in the Formula with each pass.
You would also need to add the Macro Output tool to provide your results out of the macro.
You can take all of the output from the macro and CrossTab it if you want to get the horizontal look to your data. The date would be the header and the count would be the value.
I created a workflow to demonstrate how you can achieve your goals (roughly right). It is basically what you are asking for, but I took the liberty of taking a 1st of the month view to the data report. So if you have a record in the month, that's all that it takes to appear in that month.
While building the workflow, I saw @RodL reply to you. My approach does NOT use a macro of any sort. I'm a fan of @RodL, but I'm more of a "Generate Rows" than I am a fan of a batch macro.
My approach is to calculate an ACTIVE DATE for every month between Start & End (End is set to Current Date). Using these generated rows, you can summarize by the active date and create counts. Using a cross-tab tool you can create a horizontal view of the report.
7 rows of data on input generated 80 rows of activity. 80 rows of activity were summarized into 38 monthly counts.
I hope that this works for you, but I also hope that the approach is something that you can use to help you solve problems in the future.
Alteryx ACE & Top Community Contributor
Chaos reigns within. Repent, reflect and reboot. Order shall return.
I realize this post is older, but I am working on a very similar scenario. I used the provided workflow, but for some reason on mine it is not generating a record for the current month if there is no end date. Any thoughts as to what I might be missing?