Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Qlik® Product Spotlight: Discover what’s possible. Get more from our products.
See for yourself. Register today.
tiklam_dynasys
Contributor

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

3 Replies
jaumecf23
Contributor III

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)

OmarBenSalem
Esteemed Contributor

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

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),', ')

Capture.PNG

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:

Capture.PNG

christophebrault
Valued Contributor

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)

Community Browser