Announcements
Product Release Webinar: Qlik Insider airing December 6! REGISTER TODAY!
cancel
Showing results for
Did you mean:
Creator

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

1 Solution

Accepted Solutions
MVP

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
MVP

Try this:

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

MVP

Creator
Author

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?

Creator
Author

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)

MVP

You can use a calculated dimension in that case

MVP

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

MVP

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

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 - Specialist

You can hide nulls using this technique;

-=  excludes the ''  null value

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

Master

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
Community Browser