Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
May be this:
If(GetSelectedCount(Year) = 0,
RangeSum(Sum({<Year = {2016}>} Qtty), Avg({<Year = {2016}>} 0.0000000001)),
RangeSum(Sum(Qtty), Avg(0.0000000001)))
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))
could you please share your app ??
Hi Staffan,
Unfortunately, it does not help. I get the same result.
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.
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
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'.
May be this:
If(GetSelectedCount(Year) = 0,
RangeSum(Sum({<Year = {2016}>} Qtty), Avg({<Year = {2016}>} 0.0000000001)),
RangeSum(Sum(Qtty), Avg(0.0000000001)))
Hi Sunny,
This works. Brilliant solution
Thanks friend.
No problem at all