Alteryx Designer Discussions

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

How to calculate a field based on multiple criteria

6 - Meteoroid

Hi All,

 

Would really appreciate your help in figuring out how I could best solve this problem.

 

Background story is when we onboard a client, there are multiple stages they go through and each has a target date and a RAG rating. 

Mershel257_1-1606113708099.png

 

 

I'm trying to have Alteryx calculate what stage we're currently working on for each client, and populate the "Current Phase" column with a "Y" based on the below criteria:

  • If all stages for a certain client have a RAG rating of Complete, then put Y in "Current Phase" for the row with the latest date/Live stage (see blue rows)
  • If all stages for a certain client have a RAG rating of Not Started, then put Y in "Current Phase" column for the row with the oldest date/Onboard stage (see yellow rows)
  • If there's a mixture of different RAG ratings for a client, mark the oldest stage with a RAG rating that isn't "Complete" as the current phase (see green rows)

There should be one "Y" in Current Phase column for each client.

 

I'm not sure if formulas is the way to go around this, so would really appreciate any help!!

 

Cheers!

17 - Castor

Hi @Mershel257 

 

Here is a workflow for the task.

Output:

atcodedog05_0-1606117269224.png

Workflow:

atcodedog05_1-1606116760248.png

Multi-row formula tool is used to check the RAG and mark the phase.

Hope this helps 🙂


If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

6 - Meteoroid

Hi @atcodedog05,

 

For the most part, this your workflow worked really well, thank you!!

 

I'm just encountering a couple of issues with some clients though, not sure why there's multiple Y populated for them. Any idea why and how it could be fixed?

Mershel257_0-1606179219320.png

 

Cheers!!

17 - Castor

Hi @Mershel257 

 

Can you provide this table in image as an excel and give expected output. So that i can calibrate accordingly 🙂

6 - Meteoroid

Hi @atcodedog05,

 

Amazing, thank you so much! I've attached the samples of the clients with multiple Ys.

Sheet 2 has current output, Sheet 2 Expected Output has what it should look like.

 

17 - Castor

Hi @Mershel257 

 

Can you check the attached excel it still seems like the initial excel.

6 - Meteoroid

Hi @atcodedog05,

 

It should be in sheets 2 and 3 🙂

17 - Castor

Hi @Mershel257 

 

Just checked there is only sheet2 other than sheet1.

In that output is matching the expected

atcodedog05_0-1606198649972.png

 

Can you tell what should be the expected output so that i can calibrate. Since there is series complete and break in between its getting multiple Y. 

 

If you help me out with the expected output i can calibrate🙂

 

6 - Meteoroid

Hi @atcodedog05 ,

 

I've reattached the excel file, let me know if you can see the third sheet.

 

It should like the below where there is only 1 Y per client.

 

Mershel257_0-1606198951427.png

 

17 - Castor

Hi @Mershel257 

 

Here is the updated solution. Getting output same as expected.

atcodedog05_0-1606199375145.png

 

Please check and let me know 🙂

 

Labels