3 Replies Latest reply: Jun 27, 2018 6:58 AM by Christophe Brault

# How to find products which does not sell in this year and which is new compare to last year

Hi all,

I would like to find out the products which does not sell in this year and the products which are new compare to last year.

how to do in set analysis?

Best Regard,

Louis

• ###### Re: How to find products which does not sell in this year and which is new compare to last year

Maybe something like this:

Actual year:

sum({<YEAR={"\$(=max(Year))"}>} ProductAmount)

Last year:

sum({<YEAR={"\$(=max(Year)-1)"}>} ProductAmount)

• ###### Re: How to find products which does not sell in this year and which is new compare to last year

Assume we have this:

Year,Product, Sales

2018, A , 200

2018, B , 0

2018, C , 100

2017, A,300

2017, B, 300

];

so

1) Product without sales in 2018 : B

Create a table :

Dimension: Product

Measure: Sum({1<Product={"=sum({<Year={'\$(=max({1}Year))'}>}Sales)=0"}>}Sales)

or a table with this dimension only:

aggr(only({<Product={"=sum({<Year={'\$(=max({1}Year))'}>}Sales)=0"}>}Product),Product)

or a KPI object as follow:

concat(distinct aggr(only({<Product={"=sum({<Year={'\$(=max({1}Year))'}>}Sales)=0"}>}Product),Product),', ')

2) New Products: C

Create a table

Dimension : Product

Measure: Sum({1<Product={"=sum({<Year={'\$(=max({1}Year))'}>}Sales)>0 and sum({<Year={'\$(=max({1}Year)-1)'}>}Sales)=0"}>}Sales)

or a table with only this dimension:

aggr(only({1<Product={"=sum({<Year={'\$(=max({1}Year))'}>}Sales)>0 and sum({<Year={'\$(=max({1}Year)-1)'}>}Sales)=0"}>}Product),Product)

or a KPI as follow:

concat ( distinct aggr(only({1<Product={"=sum({<Year={'\$(=max({1}Year))'}>}Sales)>0 and sum({<Year={'\$(=max({1}Year)-1)'}>}Sales)=0"}>}Product),Product), ', ')

Result:

• ###### Re: How to find products which does not sell in this year and which is new compare to last year

Hi Omar,

Another way to achieve this without using 1 identifier in the main set analysis :

For C

Sum({\$<Product = E({1<Year={\$(=Max(Year)-1)},Sales={">0"}>}Product)>}Sales)

For B

Sum({\$<Product = E({1<Year={\$(=Max(Year))},Sales={">0"}>}Product)>}Sales)