Qlik Sense: Discontinue selling the products that has not been selling enough for the past 2 years.

[Anonymous]

Hi All,

I am new to Qlik Sense. Here is the scenario given for the assignment. Need to extract the Products: Discontinue selling the products that has not been selling enough for the past 2 years.

Attached is the QVW and the Data set required.

Year 2014 - 77 Products

Year 2013 - 77 Products

Year 2012 - 73 Products

Year 2011 - 68 Products

So, the Products has  not been selling good enough where from 2011 and 2012 are 13.  I am able to find the difference in the Count but I'm not able to get the Product Names. Please get me the list of these 13 Products in Chart.

Help is appreciated always.

Have a great day! Waiting for positive response.

Thanks and Regads,

Ishita.

stalwar1‌ johnw‌

[sunny_talwar · Accepted solution]

I see how you are getting 13, but I think in reality there are only 12 products which were sold in 2 or 3 of the listed 4 years. Now they may very well be products which were sold in 2011 and 2012, but not in 2013 or 2014... would you be having those scenarios also?

I used this expression in a text box object to list all the 12 products

=Concat({<ProductName = {"=Count({1} DISTINCT NewOrderYear) < 4"}>}ProductName,' | ')

And this to get Sum(Sales) with ProductName as dimension

Sum({<ProductName = {"=Count({1} DISTINCT NewOrderYear) < 4"}>}Sales)

[sunny_talwar]

This is to cover you against this -> Now they may very well be products which were sold in 2011 and 2012, but not in 2013 or 2014...

Sum({<ProductName = {"=Count({1<NewOrderYear = {'2011', '2012'}>} DISTINCT NewOrderYear) < 2 and Count({1<NewOrderYear = {'2013', '2014'}>} DISTINCT NewOrderYear) > 0"}>} Sales)

=Concat({<ProductName = {"=Count({1<NewOrderYear = {'2011', '2012'}>} DISTINCT NewOrderYear) < 2 and Count({1<NewOrderYear = {'2013', '2014'}>} DISTINCT NewOrderYear) > 0"}>}ProductName,' | ')

[Anonymous]

Hi Sunny,

Tons of thanks for the quick reply. I will definitely chk the solution once I'm in office as I don't have laptop at home and hence license in office and home both.

Would like to ask couple of questions:

1. Qlik Sense vs QlikView data model: I tried data modelling with a few tables and use that "magic wand" sign to generate automatic data model. It came correct or not, couldn't check. Only "Synthetic keys" were not formed so thought its correct? what's your take on this? because when I tried loading these tables in QV, I had to struggle a lot to generate the correct data model. So it means QS is easier in terms of data modelling?

2. Buttons, Actions, Triggers, and a few important function used in Charts are missing in QlikSense so do we need to use Extensions for all these?

Your inputs will be highly appreciated.

[sunny_talwar]

1) I think this is probably personal preference... for me I like to take control of the script as it makes me comfortable so I would rarely use automated functions (like cross table wizard in QlikView)... but if you think that a automated script is easier for you to begin and then you can make your changes as you need, then that is something which is okay as well. But a totally automated script alone might not always work as it might interpret something which is not truly right. So, you can decide what makes it comfortable for you.

2) This is correct.

[Anonymous]

Thank you so much for the quick help Sunny. I am so grateful.

[Anonymous]

Will definetly keep your points in thoughts.