Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Data Manipulation

peterg97
7 - Meteor

Hi All, 

 

I am hoping you could help me with a data manipulation problem I have. A bit of context, I study actuarial at University and I am currently teaching myself alteryx, and want to create a claims triangle from some raw data. I have attached the excel data set for reference.

 

In the excel file I have a before tab, which is the raw data we receive, and an after tab, which is what I want to manipulate the data to look like. Disclaimer that the figures in the after tab don't match up with the before, but just give an idea of what I need. I have to do the following:

 

1. separate out the different lines of business (motor and home insurance), which I can do with filter

2. Get annual claims figures for each line of business, by summing the 4 quarters of data for each year. I was thinking about doing a left formula to get the year for each column, then sum those columns with the same year at the top, but I'm having issues with this step

3. After this is done, I need to mirror the triangle, so that the 2020 year is in the leftmost column and it works backwards. I was thinking about manual moving of columns, but I assume there is a more efficient way of doing this. 

4. Bring the claims triangles together on the same tab, with a seperate triangle for each line of business. 

 

I appreciate that this is a lot of questions, but would appreciate any help on it, even to get me started/on the right track! I've been trying different approaches, but have hit a brick wall. 

 

Thanks in advance for the help!

 

Kind Regards, 

Peter

6 REPLIES 6
TheOC
15 - Aurora
15 - Aurora

hey @peterg97 

Do you have the workflow at hand, of trying different approaches? Happy to have a look at where you hit the brick wall and see if i can help you out on that step?

Cheers,
TheOC


Bulien
peterg97
7 - Meteor

hi @TheOC,

 

I am currently at the stage of trying to figure out a way to sum each quarter for each year, as can be seen I have done this manually for the meantime, but of course this would not be ideal and I would like an automated way of doing this. I've attached the workflow, thanks in advance for any help!

 

Cheers, 

Peter

OllieClarke
15 - Aurora
15 - Aurora

Hey @peterg97 

 

I had a go at this, and I think this works

OllieClarke_0-1620902468555.png

Here's what the motor table looks like (I changed the account year to an number so its row order would be correct):

OllieClarke_1-1620902532693.png

 

I've annotated all tools, but let me know if you have any questions 🙂

 

Ollie

 

peterg97
7 - Meteor

Hi @OllieClarke,

 

This works perfectly! Thanks a lot, the annotations really helped me to understand it as well! Really appreciate it. 

 

Cheers, 

Peter

peterg97
7 - Meteor

Hi @OllieClarke,

 

Quick question - For the Home line of business, the final claims triangle starts at 1999 (since thats the year which first has claims data). Is there a way of keeping the prior years that have no data in them (i.e. have the year of account starting at 1993) but just having the claim amounts as zero for those years? Just so all the claim triangles can then be consistent. 

 

Thanks!

 

Peter

OllieClarke
15 - Aurora
15 - Aurora

Hey @peterg97 sorry, just seen this, I've updated the workflow so now every Line of Business will have exactly the same number of rows and columns

OllieClarke_0-1620983871320.png

OllieClarke_1-1620984072130.png

 

 

Labels