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

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

jharke — Wed, 25 Nov 2020 16:16:04 GMT

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.

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

sunny_talwar — Mon, 29 Aug 2016 12:56:32 GMT

Try this:

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

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

tresB — Mon, 29 Aug 2016 12:59:06 GMT

Please check the attached qvw.

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

jharke — Mon, 29 Aug 2016 13:20:14 GMT

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?

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

jharke — Mon, 29 Aug 2016 13:25:20 GMT

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

sunny_talwar — Mon, 29 Aug 2016 13:28:35 GMT

You can use a calculated dimension in that case

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

tresB — Mon, 29 Aug 2016 13:32:30 GMT

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

sunny_talwar — Mon, 29 Aug 2016 13:41:16 GMT

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

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

b_garside — Mon, 29 Aug 2016 14:02:04 GMT

You can hide nulls using this technique;

-= excludes the '' null value

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

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

effinty2112 — Mon, 29 Aug 2016 14:07:55 GMT

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

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

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

sunny_talwar — Mon, 29 Aug 2016 14:10:09 GMT

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)

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

effinty2112 — Mon, 29 Aug 2016 14:22:00 GMT

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

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

sunny_talwar — Mon, 29 Aug 2016 14:28:13 GMT

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

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

jharke — Mon, 29 Aug 2016 14:49:04 GMT

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!

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

brooksc57 — Mon, 29 Aug 2016 23:08:38 GMT

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

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

sunny_talwar — Tue, 30 Aug 2016 09:37:04 GMT

Check these two settings on the presentation tab

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

brooksc57 — Tue, 30 Aug 2016 13:30:56 GMT

Hi Sunny T,

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

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

brooksc57 — Tue, 30 Aug 2016 16:08:52 GMT

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

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

sunny_talwar — Tue, 30 Aug 2016 16:34:07 GMT

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