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

[jharke]

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.

[sunny_talwar]

Try this:

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

[tresesco]

Please check the attached qvw.

[jharke]

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]

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)

[sunny_talwar]

You can use a calculated dimension in that case

[tresesco]

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

[sunny_talwar]

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

[b_garside]

You can hide nulls using this technique;

-=  excludes the ''  null value

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

[effinty2112]

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
1	City A	City E	1	10
2	City A	City E	1	20
5	City D	City A	1	50
6	City A	City E	1	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 A	City E	1	10
City A	City E	1	20
City D	City A	1	50
City A	City E	1	60