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

[Anonymous]

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

[jaumecf23]

Maybe something like this:

Actual year:

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

Last year:

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

[OmarBenSalem]

Assume we have this:

LOAD * Inline [

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:

[christophebrault]

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)