Loop question - Need to know how many cases was open in any given week

I have a list of cases that have a Generated date and a Closed date and I need to count how many cases were open for each week of the year.

So something like:

GD=Datetimeformat([Generated Date], "%w")

CD=Datetimeformat([Closed Date], "%w")

For i=1 to 52

if I>GD and I<CD then week(i)=Week(i)+1 else Week(i)=Week(i) endif

Does this make sense? If so, How do I translate that to Alteryx?

Kind regards

Jakob

Alteryx Editions

Accepted answers

All comments

abacon

@Jmich888 Here is a great resource on creating an iterative macro, which is what you would need to create a loop within the workflow.

https://www.youtube.com/watch?v=AByfTPqrUrQ

Bacon

Jmich888

Thank you. I will have a look 🙂

Jmich888

Looping is simple after watching the video. But How do I use the loop number ([Engine.IterationNumber] to add data to a specific column (or create a column based on the loop number).

I can loop 52 time (52 weeks in a year). But how do I save the data from each loop?

I want to know, how many cases were open in each week.

Any suggestions?

abacon

@Jmich888 Once you make a workflow an iterative macro, the engine.iterationnumber becomes a variable you can use in a formula tool

Do you have a sample dataset I could use for this use case? I have an idea that I want to try out before I suggest it that might not need an iterative macro and would be simpler for you.

Bacon

dreldrel

@Jmich888 you can configure your output with the macro output tool and save it to the next iteration (Link: https://www.thedataschool.co.uk/lex-devlin/iterative-macros-in-alteryx-a-simple-example/). But in your case, I think the generate row tool should be able to achieve your goal

WirkKarl

Hi Jakob,

Yes, your logic makes sense — you’re basically iterating through each week and counting cases that were open during that period. In Alteryx, you could use the Generate Rows tool to create a row for each week between your Generated Date and Closed Date, then summarize by week to get the counts.

I’ve also tracked similar workflows using Phonexa to see which automation steps give the most accurate results — it really helps when handling large datasets like this.

Jmich888

This would be so easy if only Alteryx had arrays - Week(1), Week(2) etc and it was possible to use the iteration number for choosing column to update - Week(iteration)=123

But lets just close this thread and I will use some other tool to get what I need.

How can I close this without choosing solved?

Thanks for trying

Kind regards

Jakob

abacon

@Jmich888 It appears you changed the weeknum formula I originally had. If you change the openweekstr and close weekstr formulas to the weeknum function that was originally there, you would have the correct week number.

In the screenshot, what is commented out is what you were running through. Change it to what I put originally and show below and you have your answer perfectly.

Bacon

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15867

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703