Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Highlighted
jharke
Contributor

suppress null/zero in set analysis with more than one expression

Hello all,

We have shipments from a city to another city.

I want to have a straight table in which I want to show only the shipments that are going from City A OR going to city A, and hide / suppress the rows that do not contain City A.

See attachment for an example. Please consider that there are more than one expression (in the original file there are 6 or more expressions) --> if I put a set analysis in an expression, the results of that expression is 0, but I want to hide the complete rows of all these zero's.

(Suppress When Value Is Null in the properties is only due to dimensions, but the null is given in the expression.)

I don't want a solution in the script.

Tags (4)
1 Solution

Accepted Solutions

Re: suppress null/zero in set analysis with more than one expression

Why do we need a Aggr() function Andrew? Why not just this as the calculated dimension?

=If(WildMatch(City_from, '*A*') or WildMatch(City_to, '*A*'), Order)

18 Replies

Re: suppress null/zero in set analysis with more than one expression

Try this:

Sum({$<City_from = {'*A*'}>+<City_to = {'*A*'}>} kg)


Capture.PNG

MVP
MVP

Re: suppress null/zero in set analysis with more than one expression

Please check the attached qvw.

jharke
Contributor

Re: suppress null/zero in set analysis with more than one expression

Hello Sunny,

In the QV example I have 2 expressions and you have added the set analysis in these 2. But in my original (full) QV file I have 6 expressions. In your way I have to make set analysis in all 6 expressions.

Is there no way to put a formula anywhere once that suppresses every row where there is no A in City_from AND no A in City_to?

jharke
Contributor

Re: suppress null/zero in set analysis with more than one expression

Hello Amit,

If I add another expression, the hidden rows will appear and show '-'.

(And there is no checkbox where I can suppress those values with '-' in an expression)

Re: suppress null/zero in set analysis with more than one expression

You can use a calculated dimension in that case

MVP
MVP

Re: suppress null/zero in set analysis with more than one expression

You have to just include the condition If([sum kg]>0, NewExpression) to all new expressions and it would work fine. Like: in this case, If([sum kg]>0, Count(City_from))

Re: suppress null/zero in set analysis with more than one expression

Actually this probably would be the best way to do it. Create a new expression with your required set analysis

=Avg({$<City_from = {'*A*'}>+<City_to = {'*A*'}>}1)

and on the presentation tab, hide it

Capture.PNG

And then you just need to multiple all your expressions with the column label of the 1st expression:

Count(Order) * Col1

Sum(kg) * Col1

Partner
Partner

Re: suppress null/zero in set analysis with more than one expression

You can hide nulls using this technique;

-=  excludes the ''  null value

=sum({$<City_from={'*A*'} , City_from-={''} >} kg)

effinty2112
Honored Contributor

Re: suppress null/zero in set analysis with more than one expression

Hello,

               Maybe this straight table with a calculated dimension with the option to Suppress when value is null selected.             

=Aggr(If(City_to = 'City A' or City_from = 'City A',Order),Order) City_from City_to Count(Order) sum(kg)
4 140
1City ACity E110
2City ACity E120
5City DCity A150
6City ACity E1

60

Cheers

Andrew

PS

you can hide the calculate dimension column if you prefer:

City_from City_to Count(Order) sum(kg)
4 140
City ACity E110
City ACity E120
City DCity A150
City ACity E160