Set Analysis does not suppress zero values

[Anonymous]

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.

[stabben23]

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

[avinashelite]

could you please share your app ??

[Anonymous]

Hi Staffan,

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

[Anonymous]

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.

[sunny_talwar]

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

[Anonymous]

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'.

[sunny_talwar · Accepted solution]

May be this:

If(GetSelectedCount(Year) = 0,

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

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

[Anonymous]

Hi Sunny,

This works.  Brilliant solution

Thanks friend.

[sunny_talwar]

No problem at all