Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Counting Active Rows at Specific Time

brandt3076
6 - Meteoroid

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

4 REPLIES 4
RodL
Alteryx Alumni (Retired)

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
20 - Arcturus
20 - Arcturus

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

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
brandt3076
6 - Meteoroid

Thank you for this solution and breaking it down through a workflow. 

lohring
5 - Atom

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?

 

Labels