Alteryx Designer Desktop Discussions

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

Count instances meeting certain criteria, stop, then start counting again

cdelaura
5 - Atom

Hello,

 

I'm wondering if anyone has any ideas of how to solve the puzzle below...

 

My data starting format is one row per person with an outcome of eligible/reset indicated per week horizontally. The real data set has hundreds of names and weeks up to 38 (and will continue to grow). Simplified version below (attached is an XLS with three people)

 

1 = most recent week

9 = first (oldest) week in data set

Name123456789
AlexEligibleEligibleResetEligibleEligibleEligibleEligibleResetEligible

 

Goal: I'm looking to be able to see the average amount of 'eligible' weeks a person consecutively has before they 'reset'.

 

So for above example, I would expect Alex to show:

  • Reset after 1 week (eligible week 9)
  • Reset after 4 weeks (eligible weeks 7-4)
  • Awaiting next reset - so ignore weeks 1-2
  • Average of 1 and 4 = 2.5 weeks

I'm thinking to use Transpose name by week numbers then using the Multirow formula tool, just a little stumped how to configure the formula

 

Any ideas? Thanks in advance!

3 REPLIES 3
Jonathan-Sherman
15 - Aurora
15 - Aurora

Hi @cdelaura,

 

You could as you said use a transpose to pivot followed by a series of multi-row tools, summarising to create your average at the end!

 

JonathanSherman_0-1640793831285.png

 

 

I've attached my workflow for you to download if needed!

 

Kind regards,

Jonathan

cdelaura
5 - Atom

Thank you Jonathan! Glad to know that I was on the right track!

Jonathan-Sherman
15 - Aurora
15 - Aurora

No problem at all!

Labels
Top Solution Authors