topic Set Analysis does not suppress zero values in QlikView

Set Analysis does not suppress zero values

Anonymous — Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

I'm having an issue using set analysis together with not selecting 'suppress zero values'.

I use a pivot table with one dimension and one expression. In this expression, I use set analysis to show data only for the current year (2016), unless an other year is selected by the end user.

My expression looks like this :

If(GetSelectedCount(Year) = 0,Sum({<Year = {2016}>} Qtty),Sum(Qtty))

So, when the end user does not select a Year, I show data for 2016 only. Otherwise, data for the selected Year is shown (or at least, that's my intention).

My pivot table looks like this :

I know that there's zero values in Quarter 2016-Q4, so I want to de-select 'suppress zero values' to show this data. But, when I do this, my table also shows data for 2015 and 2017, which is what I have filtered out using set analysis :

How can I prevent this from happening and have QV show a table like this one ?

Thanks in advance.

Re: Set Analysis does not suppress zero values

stabben23 — Thu, 22 Dec 2016 14:25:47 GMT

Hi,

I dont no if this is enough, but try to add Quarter like this:

If(GetSelectedCount(Year) = 0,Sum({<Year = {2016}, Quarter={'*'}>} Qtty),Sum(Qtty))

Re: Set Analysis does not suppress zero values

avinashelite — Thu, 22 Dec 2016 14:26:44 GMT

could you please share your app ??

Re: Set Analysis does not suppress zero values

Anonymous — Thu, 22 Dec 2016 14:34:23 GMT

Hi Staffan,

Unfortunately, it does not help. I get the same result.

Re: Set Analysis does not suppress zero values

Anonymous — Thu, 22 Dec 2016 14:36:04 GMT

Hi Avinash,

The app contains a lot of company-sensitive information like budgets and turnover, so I cannot share the entire app.

I shall try to isolate part of it or create a new app with only this particular data.

Re: Set Analysis does not suppress zero values

sunny_talwar — Thu, 22 Dec 2016 14:37:38 GMT

How about this?

If(GetSelectedCount(Year) = 0,RangeSum(Sum({<Year = {2016}>} Qtty), Avg({<Year = {2016}>} 0.0000000001)), Sum(Qtty))

Keep 'Suppress Zero Values' checked on presentation tab

Re: Set Analysis does not suppress zero values

Anonymous — Thu, 22 Dec 2016 14:49:05 GMT

Hi Sunny,

Works fine for 2016, but now I am missing the zero values for 2015 and 2017 when I select one (or both) of those years.

That's why I wanted to de-select 'suppress zero values'.

Re: Set Analysis does not suppress zero values

sunny_talwar — Thu, 22 Dec 2016 14:53:48 GMT

May be this:

If(GetSelectedCount(Year) = 0,

RangeSum(Sum({<Year = {2016}>} Qtty), Avg({<Year = {2016}>} 0.0000000001)),

RangeSum(Sum(Qtty), Avg(0.0000000001)))

Re: Set Analysis does not suppress zero values

Anonymous — Thu, 22 Dec 2016 15:22:28 GMT

Hi Sunny,

This works. Brilliant solution

Thanks friend.

Re: Set Analysis does not suppress zero values

sunny_talwar — Thu, 22 Dec 2016 15:24:17 GMT

No problem at all