Counting Active Rows at Specific Time

Hi,

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 Id Start Date End 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

Developer Tools

Accepted answers

MarqueeCrew

Hi @brandt3076,

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.

Screen Shot 2017-02-02 at 10.47.43 AM.png

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.

Cheers,

Mark

Counting Active Rows.yxmd

All comments

RodL

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.

MarqueeCrew

Hi @brandt3076,

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.

Screen Shot 2017-02-02 at 10.47.43 AM.png

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.

Cheers,

Mark

Counting Active Rows.yxmd

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703