topic Re: Hide dimension when expression is zero in QlikView

Hide dimension when expression is zero

Wed, 25 Nov 2020 16:16:04 GMT

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!

Re: Hide dimension when expression is zero

avinashelite — Mon, 26 Sep 2016 10:20:35 GMT

Did you check the suppress zero values in the presentation tab of chart properties ??

Re: Hide dimension when expression is zero

dineshraj — Mon, 26 Sep 2016 10:25:37 GMT

Hi Wiebke,

Try this in text color expression

If(sum(Sales)=0,White())

Thanks and Regards

Re: Hide dimension when expression is zero

Mon, 26 Sep 2016 10:29:56 GMT

Yes, "suppress zero-values" of that expression is checked off. (there is a "V" in the box).

Re: Hide dimension when expression is zero

miguelbraga — Mon, 26 Sep 2016 10:34:16 GMT

Try this in te script, lad:

If([Year Sales] = 0, null(), [Year Sales]) as [Year Sales]

Best regards,

D.A. MB

Re: Hide dimension when expression is zero

avinashelite — Mon, 26 Sep 2016 10:36:17 GMT

will be able to share the sample data ??

Re: Hide dimension when expression is zero

miguelbraga — Mon, 26 Sep 2016 10:38:26 GMT

Check this out:

Re: Hide dimension when expression is zero

effinty2112 — Mon, 26 Sep 2016 10:39:33 GMT

Hi Wiebke,

If the field your summing to get Year Sales is called Sales then try:

Sum({$<Sales = {">0"}>Sales}

good luck

Andrew

Re: Hide dimension when expression is zero

trdandamudi — Mon, 26 Sep 2016 10:47:11 GMT

Please see the attached for the solution:

Re: Hide dimension when expression is zero

Mon, 26 Sep 2016 11:25:58 GMT

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

Re: Hide dimension when expression is zero

Mon, 26 Sep 2016 11:26:18 GMT

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

Re: Hide dimension when expression is zero

Mon, 26 Sep 2016 11:27:11 GMT

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

Re: Hide dimension when expression is zero

miguelbraga — Mon, 26 Sep 2016 11:32:28 GMT

If so, see this:

Best regards,

D.A. MB

Re: Hide dimension when expression is zero

Mon, 26 Sep 2016 11:48:28 GMT

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?

Re: Hide dimension when expression is zero

miguelbraga — Mon, 26 Sep 2016 11:55:27 GMT

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

Re: Hide dimension when expression is zero

Mon, 26 Sep 2016 11:57:18 GMT

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

Re: Hide dimension when expression is zero

Mon, 26 Sep 2016 12:04:00 GMT

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?

Re: Hide dimension when expression is zero

sunny_talwar — Mon, 26 Sep 2016 13:54:51 GMT

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

Re: Hide dimension when expression is zero

Mon, 26 Sep 2016 14:03:29 GMT

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.

Re: Hide dimension when expression is zero

sunny_talwar — Mon, 26 Sep 2016 14:05:41 GMT

Can you share what all dimensions you have?