Help needed on set analysis

[#1]

Hi All,

Hope everyone is doing great.

I need small help in indirect set analysis.

I am calculating current year Sales for products which got sales both lat year and this year.

i have tried this using set analysis as given below.

Sum({<Sales= p({<Sales= {"=Sum({1<Year={$(=Max(Year))}>}Sales)>0"}>})*

p({<Sales= {"=Sum({1<Year={$(=Max(Year)-1)}>}Sales)>0"}>})>}Sales)

but the above expression calculating sales for all the products irrespective of the P() condition.

Can someone throw some light around the issue here.

Thanks in advance,

Regards,

Nani

[sunny_talwar]

Create a new object like this

Dimension

Product

Expressions

Sum({1<Year = {$(=Year(Today()))}>} Sales)

Sum({1<Year = {$(=Year(Today()) - 1)}>} Sales)

Now you can see all the products which either have sales in current year or previous year....

Based on your requirement, it seems that you need only those where we have both current year and previous year sales available... is this true? If this is true... if you export your table to excel and filter for only those products where both the expressions are greater than 0, then you will get the list of products which should be included.... do you see any issues with this list?

[#3]

Yes,your understanding is correct. i have done this kind of testing and i am getting all the products which i want see in the chart,but i am also getting the products which doesn't satisfies the condition.

[sunny_talwar]

In the chart itself? or in your final expression? Do you have a sample you can share to show the issue or images or something that might point us to the issue

[#5]

Below are the two charts. chart 1 contains this year and last year sales for the products which are causing the problem.

Chart 2 is with the solution that you have provided.

Chart 1:

Chart 2:

Hope the above screenshots will give you some idea about the issue.

Thanks,

Nani

[sunny_talwar]

Oh so they are just showing up with 0 value? You just want the sales with 0 to go away? Try this

Under chart properties -> Add-ons -> Data handling -> Uncheck 'Include zero values'

this should remove all those products where sales is 0 from this chart

[#7]

Yes. But the issue is i have to display bottom 5 products. if i does that then the chart is not displaying any product as i have excluded 0.i have used dimension limitation feature to display bottom 5 products.

[sunny_talwar]

So now you are mentioning about the bottom 5 .... instead of inbuilt Qlik Sense option to display bottom 5, try this

If(Rank(-Sum({<Product = {"=Sum({1<Year = {$(=Year(Today()))}>} Sales) > 0 and Sum({1<Year = {$(=Year(Today()) - 1)}>} Sales) > 0"}>} Sales)) < 6, Sum({<Product = {"=Sum({1<Year = {$(=Year(Today()))}>} Sales) > 0 and Sum({1<Year = {$(=Year(Today()) - 1)}>} Sales) > 0"}>} Sales))

[#9]

Apologies for that. but i have done this already and getting the required results. but due to the large volume of data i am trying to use the inbuilt function by writing set analysis to display bottom 5 products.

Is there any way that i can achieve this using the inbuilt functionality?

Thanks,

Nani

[sunny_talwar · Accepted solution]

How about trying something like this

If(Sum({<Product = {"=Sum({1<Year = {$(=Year(Today()))}>} Sales) > 0 and Sum({1<Year = {$(=Year(Today()) - 1)}>} Sales) > 0"}>} Sales) = 0, 100000000, Sum({<Product = {"=Sum({1<Year = {$(=Year(Today()))}>} Sales) > 0 and Sum({1<Year = {$(=Year(Today()) - 1)}>} Sales) > 0"}>} Sales))

and then try the inbuilt function... problem is that, in the eyes of Qlik, 0 is part of the bottom 5 products that you are looking for... may be force the 0's to be very large compared to other values

[#11]

Ok. i will try this solution tomorrow as i came out from my office and will let you know.

Thanks Sunny .

Regards,

Nani