Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
tiklam_dynasys
Partner
Partner

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
Creator III
Creator III

Maybe something like this:

Actual year:

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

Last year:

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

OmarBenSalem
Partner
Partner

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
Specialist
Specialist

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)

Inscrivez vous à ma Newletter Qlik
DoNotMissQlik- Connect with me on Linkedin