Data labelling, combining and trend analysis

[freemanooo]

Hello!!

I'm a fairly new Alteryx user who knows the basics. I have a set of test product data attached, basically I have multiple products with unique account numbers that differentiate the product. Each product can change behaviour (i.e. column A in the attached TEST DATA file) within multiple rows by month. Though no month column should have the same account twice as one product can only be a certain type at a certain time (defined by column C which is product type).

What I'm trying to do is:

1) Combine unique accounts into one row that then shows the movement in that one account

2) Adding an identifier (based on column C) to each account by month to show its type

3) The end goal is then to identify when and only when, a product moves from Fixed type to SVR type.

4) Then by account, I need data to show how many months AFTER fixed that a product was SVR before it changed or no longer existed. Note I only need to know this for fixed t0 SVR, all other variations don't matter i.e. rows 14 and 15 are the scenario to map, excluding the others i.e. I don't care about SVR to fixed regardless of when this happens.

The dream is then say for a massive list of products where I can identify for products following that trend, for how many months they stayed SVR after fixed.

Any help really appreciated love the community and have taken so many solutions from this so thanks for your time.

[benakesh]

Hi @freemanooo ,

Check the wf and output  and let us know  if it   helps   .  

Acct 1005  changed from fixed to svr to fixed . I think the col "Example"  for account 1005 should be : Fixed to svr to fixed .

[danilang · Accepted solution]

Hi @freemanooo 

I think this does what you're looking for.  

I start off by transposing the data and removing the nulls.  The formula tool converts the date into Alteryx format and determines the type of value, Fixed, SVR, etc.  The month number Multirow tool generates a sequential monhh number for all the values per account.  This is used in the Cross tab to ensure that all the columns show up in the correct order.  The Dynamic Rename tool replaces the MonthNumber column headers with the actual date.

The bottom container determines the SVR Month count.  The first Multi-row tool determines if a change from Fixed to SVR occurred for that row.  The join is used as a filter to keep only the affected rows and the months in SVR state are counted.  This output is joined back to the Cross Tabbed data to append the count.  The results look like this. 

 

This image only shows the last few months, but the results contain all the months.  I added an identifier after the value (T)

, (S), etc, since you didn't specify how you wanted the type represented in the single row output.  

Dan

[freemanooo]

Thanks so much this worked great and I tested on a 3 yr and 10 yr dataset, thanks again!!!