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]

The dimension is not called YearSales.

The expression to get the YearSales is a lot more complex. I named it Year Sales, but the expression is:

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

I cannot just copy your example, because then I would lose all the other information I've got there (being the "=" signs).

[#3]

I am not allowed to touch the script I am afraid...

[miguelbraga]

If so, see this:

Best regards,

D.A. MB

[#5]

Thanks for the file, I am trying to re-write it (sorry for my amateurism).

My expression is: sum({$<Year=,Month=,Week=, YTD=>} Sales)

And the name of my expression is "Year Sales"

How do I get it to work with your fomula? If I re-write it like this, I lose the entire expression of my "Year Sales" (the entire expression stated above)?

Sum({<[Year Sales] -= {0} >} [Year Sales])

Qlikview won't know what is behind the Year Sales if I don't explain it anywhere?

[miguelbraga]

I thought that would be a simple value. In your case, as I see, isn't simply as I thought, so... try this expression:

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

Then rename it as "Year Sales". My given expression worked well because it excluded all the values that are 0 from the table. With my new expression you must check the "Supress Null Values" that appears in the Dimension Tab in your chart. Try and tell me if it solved your problem

MB

[#7]

Hi ,

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

Use this in your expression's condition space ,

It will calculate only if the above condition satisfies.

Regards,

Balakrishnan.R

[#8]

Ok, so now I have null()-values as a result of my expression instead of zero's. This is a step closer, but how can I now hide the null()-values? When I check the "suppress zero values" in the presentation tab it does not do anything...

And I cannot use the "suppress zero values" in the dimension tab, because it is not directly linked to a dimension.

I should be able to tell a dimension to become null() of the expression stated above is zero. I could then check the "suppress when value is null" in the dimension tab. I would do this for the "name" value => change name to null() when expression of Year Sales = 0.

Do you know what I mean?

[sunny_talwar]

Do you only have one dimension? If you do, then may be try this

Aggr(If(Sum({$<Year=, Month=, Week=, YTD=>} Sales) > 0, Name)

and then select 'Suppress When Value Is Null' option on the dimensions tab

[#10]

No, I have multiple dimensions.

That's why I need a link between my expression and a specific dimension. I suggest the dimension "name" since this one should always contain a value, unless I make it become null() with the use of an expression.

[sunny_talwar]

Can you share what all dimensions you have?