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 · Accepted solution]

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)

[effinty2112]

Hi Sunny,

                    I used an Aggr because I can't get Aggr out of my head!

You know the old song that goes ...

The sun comes up, I think about you

The coffee cup, I think about you

I want you so, it's like I'm losing my mind

Wrap the lyric in Replace('...','you','Aggr') and you get the idea. Except don't - the apostrophes in the lyric mess it up.

Cheers

Andrew

[sunny_talwar]

Hahahahaha that song is hilarious

I still think that calculated dimension isn't the best approach here, but I guess that's what the OP is happy with

[jharke]

The Calculated Dimension is the easiest-to-understand way. And I don't have to change my other expressions and can add expressions later when I don't remember any more that in this particular table all expressions must have the same set analysis...

Thank you!

[brooksc57]

Hi Sunny T,

love the T1 technique...it is way simpler than long formula's for sure...however, I have a bizarre thing going on.

I've got nulls (or could have made zero's)...I represented them with the word Null just to show they were NULL and not Missing...I replicated that in your example to make sure my Null and your 0 were the same thing and they were.

so I don't understand why my NULL rows won't suppress...see image.

my T2 and T3 are actually variables multiplied by T1.   (as seen in the definition portion of attached pic)

T1 = if($(vUGM)>0,1)

T2  = $(vUGM) * T1    --- variable has set analysis is for a particular user

T3 = $(vUGT) * T1     ---- variable has set analysis that is all users

as you see I'm getting the desired result for T1, T2, T3.

When T2 > 0 then T3 shows up with #'s...otherwise T3 is listed as null.

However, while displayed as I'd expect it won't supress zero values -- the pres page is marked correctly.

perhaps you've run across a setting or something I don't have right in 'security' or know something about set analysis and it's affect in this regard???

thx

Brooks

[sunny_talwar]

Check these two settings on the presentation tab

[brooksc57]

Hi Sunny T,

that is what I had/have...very odd, huh.

[brooksc57]

okay, to help others...

I think I've stumbled on the answer...still investigating, but think I've got it.

I stripped out all other variables from my qvw...and voillah it is now working.

I am 99% sure my issue was the "green bar" coloring I wrote to color every other row.

I probably need to add something to that formula as well.

point is that it IS indeed another factor that was obvious within the basic setup itself.

thanks again

[sunny_talwar]

I guess the expression used RowNo() function? I have heard RowNo() causing issues such as those