Hide dimension when expression is zero

[#1]

Hi all,

I have a chart with multiple dimensions (Name and Phone number).

I have one expression, called "Year Sales". The expression shows the sales of that year (a bit more complex than sum(Sales), but that does not matter).

I want to hide all names without any sales, basically all names with Year Sales = 0. In my example below, the row of customer C should be gone.

Guess I could do this with a dimension "supress when value is null", but I do not know how to re-write the calculated dimension for "Name" so that every line with "Year sales = 0" would become "Null()".

If possible to solve it a different way, that is also fine with me.

Thanks in advance!

[#2]

It sounded promising, but it did not do the trick...

They are still there...

I tested it a bit and when I type sum({$<Year=,Month=,Week=, YTD=>} Sales)=0, the entire column dissapears, even though there are values not equal to 0. When I type <>0 everything stays the way it was before I entered the condition.

[#3]

customer number

customer name

phone number

invoice date (in the date value: date(invoice date, 'DD/MM/YYYY'))

invoice number

[susovan]

You can try this expression as a conditional dimension.

=Aggr(PurgeChar(Expression Field,'0'),Dimension Field)

[#5]

I have multiple dimensions, which one should I use as "Dimension Field"?

[susovan]

You can use the Name as dimension field.

[#7]

This just calculated my expression.

So in the example in my first post, A B and C would have changed to 90$ (the sum of 40$ and 50$).

[gilbertomedeiro]

Hi Wiebke,

Let me try help you in this issue. If I understood the problem, you want some expression that you just need to copy and paste in expression in your pivot table, isn't it? So, if that's true and if there both fields (Year Sales, Sales) exists, try this expression:

     sum({$<Year=,Month=,Week=, YTD=,[Year Sales] = {'>0'}>} Sales)

But there is no [Year Sales] field because you renamed it to Sales, try like that:

     sum({$<Year=,Month=,Week=, YTD=, Sales = {'>0'}>} Sales)

I hope it's can solve your problem!

Regards,

Gilberto

[#9 · Accepted solution]

Found my own solution!

I used a calculated aggr.dimension. I changed my first dimension "Name" into:

=aggr(if(sum({$<Year=,Month=,Week=, YTD=>} Sales)<>0, Name), Name)

And I then used the "Suppress When Value Is Null"-option in the dimension tab.

It worked