topic how to exclude certain values from one dimension in QlikView

how to exclude certain values from one dimension

chriscools — Sat, 16 Oct 2010 14:58:52 GMT

Hello,

i want to make a bar chart of orders that are in the warehouse.

i habe a tabel where i can see every order and in which fase it is.

now i want to make a bar chart to see how many orders there are in each fase.

but the last fase is completed, and about 90% of the orders in the table at this moment are completed.

so the relevant fases (not yet begon, picked, packed,...) have a really tiny bar...

is it possible to show the fase as dimension but to exclude 3 or 4 fases (there are 12 in total, and 8 i want to see).

thanx for your help!

grtz,

chris

how to exclude certain values from one dimension

Sat, 16 Oct 2010 15:21:31 GMT

Hi Chris,

there are more than one posibility to show only few bars in a chart:

one is using "Dimension Limitations" on the Presetation tab --> check "Max Visible Numbers" (and) "Show x-axis"; together with your favorite sorting

another (I think better to solve your problem) is using SET Analysis in your formula like

count( {$} <{fase = {'not yet begon', 'picked'} }> } orders)

Means: the {$} = the actuel user selection, BUTcount only these orders with fase = 'not yet begon' OR 'picked';

P.S.: the syntax above is "handmade", must not be totally correct

Hope this hels

Roland

how to exclude certain values from one dimension

jonathandienst — Sat, 16 Oct 2010 18:17:30 GMT

Chris

I believe Roland's expression should read

count({$<fase = {'not yet begon', 'picked'}>} orders)

This expression will include the value in the curly braces. If you wish to exclude certain values, then use

count({$<fase -= {'not yet begon', 'picked'}>} orders)

Again, correct the expression so that it is correct for your data.

Jonathan

how to exclude certain values from one dimension

chriscools — Sun, 17 Oct 2010 07:33:38 GMT

Goodmorning,

to show only the values i want works perfectly!!! thank you all very much!

however to exclude values that doesn't seem to work... if i put a "-" after fase and before the equasion

it shows everything including the values between brackets.

have you got a clue what this might be?

this is the formula:

count ( {$ < [status bestelbon] - ={'aanvul' , 'aanvul ok'} > } bestelbonnummer)

thanx!

chris

how to exclude certain values from one dimension

Miguel_Angel_Baeyens — Sun, 17 Oct 2010 11:36:57 GMT

Hello Chris,

It may be a typo but make sure you have the following without spaces or blanks in the operator

count ({$< [status bestelbon] -= {'aanvul', 'aanvul ok'} >} bestelbonnummer)

Hope that helps!

how to exclude certain values from one dimension

chriscools — Sun, 17 Oct 2010 11:49:44 GMT

thanx!

it was indeed a typo, i left a blank between - and =

now it works perfectly!!!

than you all for the answers!

grtz

how to exclude certain values from one dimension

jonathandienst — Sun, 17 Oct 2010 13:32:40 GMT

Chris

I think you have a space between the - and = signs. Try it without the space

Jonathan

Edit - beaten to the punch!

how to exclude certain values from one dimension

Mon, 14 Feb 2011 15:09:38 GMT

Hi Miguel/Jonathan

it was so informative to remove a certain value from a dimension from showing up in the chart. but am confused when we have mutiple dimensions in x-asis. we have two.. region and year.

We might have junk values having '-' in dimensions. now the issue is when we have multiple dimensions. Based on the information u have provided, I have changed my expression to

Avg

({$<[YEAR_NUM]-={'-'}>}TAT)

and it helps in removing those values with year value as - . but the second dimension is region and i want to eliminate regions with value - ..so i need an expression which tells to show the avg(tat) where region value not equal to - or year value not equal to -

how to exclude certain values from one dimension

Miguel_Angel_Baeyens — Mon, 14 Feb 2011 16:17:38 GMT

Hello,

I think I'm missing something. That "-" value means "null", and usually is not an intended symbol. That means that some value that is not in the dimension shares fields with expressions (e. g. in a calculated dimension).

But in regards to the syntax in set analysis, that would be

Avg({< [YEAR_NUM] -= {'-'}, Region -= {'-'} >} TAT)

If those values shouldn't be aggregated you can try going to the chart properties, dimensions, select YEAR_NUM and Region and click on "Suppress null values".

Hope that helps.