topic Re: Help needed on set analysis in QlikView

Help needed on set analysis

Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Help needed on set analysis

antoniotiman — Mon, 11 Sep 2017 15:39:55 GMT

Hi Nani,

maybe

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

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

Re: Help needed on set analysis

sunny_talwar — Mon, 11 Sep 2017 15:45:36 GMT

May be just this

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

Re: Help needed on set analysis

effinty2112 — Mon, 11 Sep 2017 15:45:54 GMT

Hi Nani,

Try:

Sum({$<ProdID = p({<Year = {$(=max(Year))}>})>*<ProdID = p({<Year = {$(=-1+max(Year))}>})>}Sales)

Where ProdID identifies products.

good luck

Andrew

Re: Help needed on set analysis

Mon, 11 Sep 2017 16:03:49 GMT

Thanks all for the responses. I am trying everyone's solutions. will update everyone once i got the right solution.

Thanks again.

Regards,

Nani

Re: Help needed on set analysis

Mon, 11 Sep 2017 17:31:13 GMT

@antoniotiman‌ and @stalwar1 both your answers are giving same result but still i am getting some products in my bar chart which dont' have sales either last year and this year.

i am trying to understand if i miss anything here.

Thanks,

Nani

Re: Help needed on set analysis

sunny_talwar — Mon, 11 Sep 2017 17:38:09 GMT

When you say this year? do you mean the selected year and last from the selected year?

Re: Help needed on set analysis

Mon, 11 Sep 2017 17:45:33 GMT

No i don't have any year filter.This year means current year irrespective of any selection.

Re: Help needed on set analysis

sunny_talwar — Mon, 11 Sep 2017 17:47:15 GMT

May be this

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

Re: Help needed on set analysis

Mon, 11 Sep 2017 17:59:00 GMT

Still same results getting few products which doesn't have sales.

Re: Help needed on set analysis

sunny_talwar — Mon, 11 Sep 2017 18:02:39 GMT

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?

Re: Help needed on set analysis

Mon, 11 Sep 2017 18:13:39 GMT

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.

Re: Help needed on set analysis

sunny_talwar — Mon, 11 Sep 2017 18:14:45 GMT

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

Re: Help needed on set analysis

Mon, 11 Sep 2017 18:28:41 GMT

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

Re: Help needed on set analysis

sunny_talwar — Mon, 11 Sep 2017 18:32:39 GMT

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

Re: Help needed on set analysis

Mon, 11 Sep 2017 18:36:35 GMT

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.

Re: Help needed on set analysis

sunny_talwar — Mon, 11 Sep 2017 18:40:24 GMT

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

Re: Help needed on set analysis

Mon, 11 Sep 2017 18:43:49 GMT

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

Re: Help needed on set analysis

sunny_talwar — Mon, 11 Sep 2017 18:50:31 GMT

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

Re: Help needed on set analysis

Mon, 11 Sep 2017 18:53:05 GMT

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

Thanks Sunny .

Regards,

Nani