Hello Everyone, I tried to ask this question with no luck and I am hoping that a different way of explaining it is possible. Please see picture for reference. There can be as many "Total Opens" for one specific person, once the "Auto Open" happens for a specific person, the rest must become "Total Opens." Below, for Kelsey, the red "1" should be in the Total open column because after the first "auto- open" all must become total opens, for that specific person only. Under Kelsey, you can see Robyn, which has an accurate way of how it should be done. It does not matter that Total Opens came before the Auto Opens, just once that auto open does occur for that specific person, I need a formula that would change all other auto opens, to total opens.
This is an interesting usecase. Here is my take on this. And I am solving it a bit differently without multi-row formula since this will be easy for me to explain and easy for others to understand the logic.
Just a suggestion from my end before building a workflow build the logic for it either in excel or as steps. Each step should be such a way that it can be implemented (if not divide into more smaller steps that can be implemented)
As per your explanation
Pre Step: Implement the defensive design step mentioned by @Aguisande. Convert the date to Alteryx date, apply sort by person and sort by Alteryx date ascending.
1st Step: The logic you want to apply is on the rows where Auto Open is 1. So you can use a filter to keep where Auto Open is 1
2nd Step: We only want to keep the first row of Auto Open as it is. And for rest Total Open should be 1 and Auto Open Null. How you can convert to Alteryx logic is by using unique tool that way first row and rest rows will be separated.
3rd Step: For rest Total Open should be 1 and Auto Open Null
4th Step: In excel it would have been clear the filter. Here its merge all the data back together using Union tool.
5th Step: Sort the records based on the person and Alteryx date descending.
Note: this also handles non-consecutive entries.
Hope I was clear if not please let me know.
Hope you learnt on how to build logic for usecases. Once you have done that it's easy to translate it in Alteryx and also easy to explain it to ops team & stake holders.
And as @mbarone suggested its always a good idea to get familiarized with the tools available. You can go through interactive lessons and explore possibilities in Alteryx.
@atcodedog05@Aguisande@mbarone@Pingu Thank you all so much for taking the time and giving input! I am so impressed and grateful for the alteryx community. I consistently am impressed with how many ways there are to tackle this. When it comes to alteryx, I feel very confident in the tools I am familiar with, but there is always a learning curve when it comes to things you have not done before and I am so thankful for all the help! I still have to go through some of the ideas offered on the page, and will accept as a solution once I am done trying them!