Data Manipulation

[peterg97]

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

[TheOC]

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

[peterg97]

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 · Accepted solution]

Hey @peterg97 

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

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

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

Ollie

[peterg97]

Hi @OllieClarke,

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

Cheers, 

Peter

[peterg97]

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]

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