10 Replies Latest reply on Apr 21, 2018 5:39 PM by Tony Fahd

# how to track year over year change

so i am attaching a workbook where i have list of my customers in 2017 and 2018. each customer has a category. some customers stayed in the same category in both 2017 and 2018, bust some customers change categories. so for example customer cda was in category A in 2017 and switched to category E in 2018. some customers didn't change category (customer ty)

how can i track those changes. i want to find a way to be able to show how many of customers are making changes from A to E or any different categories. i want to be able to show percentages as well. something like 20% of my customers changed from A to B in 2018 and so on, and want to show new customers in 2018 (customer CDS)and customers who had a category in 2017 and no longer in 2018(customer io)

any help is much appreciated.

• ###### 1. Re: how to track year over year change

Hi Tony

Not exactly sure, but does this help?

Thanks,

Shin

1 of 1 people found this helpful
• ###### 2. Re: how to track year over year change

Shin, this is exactly what i needed.Thanks!!

• ###### 3. Re: how to track year over year change

Hi tony,

You are very welcome.

Regards,

Shin

• ###### 4. Re: how to track year over year change

question i have is how do you read the table? so if i am looking across, does this tell me that total customers who had category A is 2017 is 4 and that 2 switched to B and 2 customers switched to E?

does this view along tell me that number of customers in 2018 who are B category are 4?

how do I read the Null?

the reason i am asking is because the spreadsheet that i have has about 1 million rows. if i do a count distinct of my customers in 2017 and select category A for example, this number doesn't match the grand total when i look at it using the calculations you created and look across for 2017. so the grand total in 2017 (using your calculations) when i look across is different than the count of distinct customers when i just simply select a category, a year and count of customers. any idea why this is happening?

• ###### 5. Re: how to track year over year change

I am not quite sure what is your issue without seeing the data, but

If you are filtering specific year, you need to put filter as context.

Shin

• ###### 6. Re: how to track year over year change

thanks again for the help.

so see below what i mean: the number of unique customers is showing 226,180 in this calculation

but when i do distinct count of customers for GTR category in 2017 it's showing a different number: below i am filtering on 2017 and the GTR category and doing a simple distinct count of customers. any idea what could be wrong?

• ###### 7. Re: how to track year over year change

Hi Tony

Are you able to attach the sample workbook which has sufficient number of data to replicate your issue?

Shin

• ###### 8. Re: how to track year over year change

Hi Shin i am attaching a sample workbook. the number of customers who had INT category in 2017 is 8264 when using the fixed calculations and looking at year over year change (sheet 1 in workbook)

but when i do count of unique customers in 2017, the number is 8,496 (sheet 2 in workbook)...not sure what is the issue.

• ###### 9. Re: how to track year over year change

Hi Tony

On your new data, there are multiple categories in same year on one customer.

And all customer has INT category in 2017.

When I change the LOD formula to reflect combination of customer x category,

INT becomes 8496, but I am not sure the result make sense or not.

I am afraid something is not matching on your real data between data structure and your logic

Rgards

Shin

1 of 1 people found this helpful
• ###### 10. Re: how to track year over year change

Shin, I found a way to only pull the last category in 2017 and remove the previous one. your work is amazing and pretty impressive. Thanks a bunch