2 Replies Latest reply on Jul 18, 2018 1:32 PM by Vishnu Sekhar

# Only analyse products that have full data over a number of years

Hi there!

I am working on an analysis on products. I have an excel, currently 3 sheets for 3 years (I put them together in the example). Every sheet has a series of product IDs, with for each a few metrics (category, sales, margin, etc). Not all products are sold in all years.

I would like to figure out how products developed over the years, for example their sales. E.g. I want to make a bar chart, with for each product category, the average sales for the past 3 years.

However, I ONLY want to do this for the products which have been sold in all 3 years. See my example. For some products (product ID 3, 8 and more) I have data for all 3 years. For others, only 1 or 2  years.

How do I perform analysis on products that have 3 years (or at least 2 out of 3) data available?

Thanks a lot!

Note: I just realised the categories aren't consistent over the years, but they should be. Each product ID always belongs in the same category.

• ###### 1. Re: Only analyse products that have full data over a number of years

Hi Reinoud,

One potential way to do this is to create a filter that eliminates products that do not have 3 years of history.  Create the following calculated field and filter for TRUE:

{fixed [Product ID]: countd([year])} >= 3

This will exclude those products that only have data for 1 or 2 years.

Thanks

Vinnie

• ###### 2. Re: Only analyse products that have full data over a number of years

Hi

You can get this done by the following calculations/LOD's

Our first aim is to count all the distinct years in the dataset. For that use the below LOD calculation

1. LOD to calculate the distinct years in the dataset.

2. Calculate distinct years for ever product or in other words count of years in which the product was sold.

3. Put a condition to check these values

And this will give the solution you are looking for.

Please be kind to mark the question as answered if the solution works. This would help other community members as well.

Regards

Vishnu