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 Post your workflow you are using with the test data, the workflow I sent got it to work so I want to see what's different. You might be overcomplicating it, this is pretty advanced but the tools used within alteryx for this make it much easier than you would think.

UPDATE: My guess is the dates are giving you an issue, here is an updated workflow that accounts for the date format and gets you the desired output. The workflow I just posted is updated to reflect this change.

Bacon

[Jmich888]

I am still not getting the of open (active) cases per week.

I had to modify it slightly because I do not have the "DateTimeWeekNum([Created Date])" So I use DateTimeFormat and ToNumber - should be the same.

I have attached the modified workflow and some test data

[abacon]

@Jmich888 The date time tools weren't reset to your needs with the date formats. It looks like with the format of your dates in the test data, you don't need the datetime conversions. Make sure, when running the workflow that the dates are in the yyyy-MM-dd format - if they are already like that, then you don't need to convert, if not, then you will need the datetime conversion. I removed the conversion steps and ran your workflow with the test data and didn't get an error. I added them because the data you originally posted was yyyy-MMM-dd format, again you will have to customize based on your needs in the real data.

What version of Alteryx are you on?

Bacon

[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]

Thanks, but the result is completely off. For example case 105 shows as open (active) in week 1, but it wasnt created until end of September, so it was not open in week 1.

[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