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

[Jmich888]

Hi

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 

next

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

Kind regards

Jakob

[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

[abacon]

@Jmich888 Does this get you the result you need? I used a generate rows tool to find the weeks a 'ticket' was open, then grouped on that weeknumber. From what I am reading, this should do the trick 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

[Jmich888]

It does not seem to work.

Now I also get an error - The Iteration input "" is not present.

It seems like macros are not the solution because:

1. I cannot parse any fields to the macro - Fields [Date Created] and [Date Closed}

2. I cannot create fields based on a variable (Iteration number) [Week 1-52]

My data looks like this (Simplified)

C

Case No	Created date	Closed date
100	2025-Jan-04	2025-Jan-28
101	2025-Jan-06	2025-Jan-16
102	2025-Jan-10	2025-Mar-22

(+3500 rows)

And the output I want from that is

Week	Count of open cases	This column is for explaining here only. Not wanted as part of output
1	1	100
2	3	100, 101, 102
3	3	100, 101, 102
4	2	100, 102
5	2	100, 102
etc

[abacon]

@Jmich888 I would not go the macro route seeing your test data. See my last post with the workflow, that logic will get you want you want. You may have to rename the fields, but use the logic to get your output. Additionally, I know you said the column you show for explaining isn't needed in the output, but if you do want it, you can. Just Concatenate the Case_no column in summarize tool, use a comma, and name it what you want. That will get you the exact output you showed.

If you it works for you, please mark it as the solution so others may find it faster. 

Bacon

[Jmich888]

I cant get it to work. It just says "The value did not change after the loop expression" at the Create rows tool.

Looks like I just have to make 52 formulas and 52 summarize to check if a case was open in each week of the year and then count the 52 week columns.