Counting items when criteria is met

[emilia_siatrak]

Hello,

I limited experience in advanced formulas and am struggling with this one.

I have a set of data with three dimensions: Customer ID, Product Code, Purchaseyear (3 years: 2017, 2018, 2019).

I am trying to build an expression that will answer a question: How many products reach overall sales number of 5 items or less each year. Effectively I would like to count only those products where sale per year is less or equal to 5 units. 

I have gone down the route of  assigning 1 or 0 if the count is 5 or more for each of the Purchase years as below:

if(count({$<Purchaseyear = {2017}>} [Product ID]) >= 5, 1,0)

I then wanted to sum the output but can't get it to work properly.

I expect there is a better way of tackling this. I would be grateful for any suggestions or examples of statements.

Thank you!

I need to correct myself - sorry! I have realised the question should be different.

I need to find out how many products were purchased by less than 5 individual customers in each year.

I have attached dummy data set and based on that I would want to see this summary:

2016        2017         2018

3                 3                4

[Kushal_Chawda]

Would you be ale to share sample with expected output?

[martinpohl · Accepted solution]

with your expression you will get many vanlues for many product ID, so what to do?

tell your expression:

sum(aggr(

if(count({$<Purchaseyear = {2017}>} [Product ID]) >= 5, 1,0)

,{Product ID]))

[emilia_siatrak]

Thanks! It works... and it made me realised I have asked the question wrong.