topic Re: Expression in QlikView

Expression

Wed, 19 Mar 2014 08:56:26 GMT

Hi all

I got this table:

Now I got a problem. Got 2 expressions first 1 for the total km, second one for km outside.

However in my second expression there is no data for vehiclecategory B but it keeps filling it with 0's. Is it possible to solve this?

Do I need to start looking my excel? Script?

kind regards

Re: Expression

alexandros17 — Wed, 19 Mar 2014 09:05:38 GMT

If you don't want 0 as value (if you have yet tried with missing and null value settings) you may write:

if(myexpression=0,'Not Available', myexpression)

Re: Expression

Anonymous — Wed, 19 Mar 2014 09:11:12 GMT

Try this:

=if(b='NULL' or IsNull(b) or b = 0,'', b)

Regards
Stefan

Re: Expression

Wed, 19 Mar 2014 09:25:01 GMT

with your expression it comes up like this

if(Km_Outside=0,'Not Available', Km_Outside)

Re: Expression

Anonymous — Wed, 19 Mar 2014 09:36:38 GMT

=if(b='NULL' or IsNull(b) or b = '-' or b = 0,'', b)

Re: Expression

jonathandienst — Wed, 19 Mar 2014 09:39:43 GMT

Assuming that b is a field and a dimension, I think that if b has values with some combination of the other dimensions (eg in Total KM in your example), you will be unable to suppress the display for combinations that contain no values (eg KM Outside)

All I can suggest is that you change what's displayed for the null and/or missing value on Properties | Presentation (eg a space, so nothing is displayed). But the column will remain.

HTH

Jonathan

Re: Expression

Wed, 19 Mar 2014 09:40:42 GMT

that was exactly what I was thinking. But I really hoped there was a solution.

Re: Expression

jonathandienst — Wed, 19 Mar 2014 09:45:09 GMT

You may be able to do something with a calculated dimension, which return null for the 'empty' combinations, but its likely to be quite complex as it will need to Sum(Aggr(...)) the expressions you want to test.

If you post your expression and the existing dimensions, I could give it a try, if you need some help.

Re: Expression

Wed, 19 Mar 2014 09:48:59 GMT

like this?:

=if(Production.VehicleCategory = 'B'='NULL' or IsNull(Production.VehicleCategory = 'B') or Production.VehicleCategory = 'B' = '-' or Production.VehicleCategory = 'B' = 0,'', Production.VehicleCategory = 'B')

Re: Expression

Wed, 19 Mar 2014 09:54:45 GMT

hi Jonathan,

That would be really kind of you!

Dimensions:

DivisionName

Week

Vehiclecategory

Expressions:

sum(total_KM)

sum(total_outside)

Re: Expression

jonathandienst — Wed, 19 Mar 2014 09:54:54 GMT

That's not what I had in mind. Something more like

If(Sum(Aggr(<the chart expression>, Production.VehicleCategory, Week, DivisionName)) <> 0, Production.VehicleCategory, null())

Then enable Suppress nulls for that dimension.

You might need something like Sum(TOTAL <Week, DivisionName> Aggr(...))

Re: Expression

jonathandienst — Wed, 19 Mar 2014 09:58:28 GMT

My previous reply was posted before I saw your post.

So these two columns are two expressions? Then what I had in mind won't work, I am afraid. I had assumed that Total KM and Km Outside were coming from a field.