topic Re: Excluding names from a Chart in QlikView

Excluding names from a Chart

Fri, 07 Feb 2014 11:29:08 GMT

Hi All,

I'm struggling to exclude certain names from a BAR CHART n X-axis, I have created

Field is Employee Names on X-axis and taken Sum(Salary) on Y-axis.

As it stands, the Bar Chart is displaying every person's name in the company but i'd like to hide a few of them if possible so they no longer feature in the Chart...

in this i have some names like Ram, Vidya,Gopal,Bhavin,Amit,Sneha,Nisha,Isha,Sharan,Rekha only..

I am new QlikView please suggest me...

Thanks

venu gopal

Re: Excluding names from a Chart

luis_pimentel — Fri, 07 Feb 2014 11:39:35 GMT

You can use a calculated dimension. In the Chart Properties, Dimension Tab, instead setting just the name of the dimension you can write an expression like

IF(EmployeeName<>'Vidya',EmployeeName)

or any other condition you can think of. That would displays all employees but Vidya.

Or, in the Dimension Limits Tab, you also can Set some limits for the values of your dimensions.

Hope that helps.

Luis.

Re: Excluding names from a Chart

jagan — Fri, 07 Feb 2014 11:44:15 GMT

Hi Venu,

You can exclude the names by using the -= operator, qlikview expression editor shows it as error(highlighted in red color) but it works

=Sum({<EmployeeName-={'Vidya', 'Sneha'}>}Salary)

The above expression excludes Vidya and Sneha, if you want to exclude more persons then include in a comma separate format.

Regards,

Jagan.

Re: Excluding names from a Chart

luis_pimentel — Fri, 07 Feb 2014 11:46:52 GMT

Hi!

just pointing that if you use this solution (which it works fine), you must use this set analysis expression in ALL the expression you have in your chart (including color expressions, formatting, ...)

Regards,

Re: Excluding names from a Chart

stevedark — Fri, 07 Feb 2014 11:52:25 GMT

Hi Venu,

There is a set of syntax called Set Analysis that makes this simple. If you Google on it you should find lots of articles.

If you want to specify which names are included the syntax is like this:

Sum({<Employee*={'Vidya','Gopal','Bhavin')>}Salary)

If you want to exclude some then the syntax is:

Sum({<Employee*={*}-{'Vidya','Gopal','Bhavin')>}Salary)

Rather than listing each employee in the statement you will be better off adding a new field in the load script, perhaps called IncludeInChart. You could do this with an inline table and ApplyMap.

Map_IncludeInChart:

MAPPING LOAD

Employee,

'Yes' as Include

INLINE [

Employee

Vidya

Gopal

Bhavin

];

Salaries:

LOAD

Employee,

ApplyMap('Map_IncludeInChart', Employee, 'No') as IncludeInChart,

[... rest of your load statement here ...]

By doing that you are then moving some of the logic to load time and simplifying your Set Analysis expression to being this:

Sum({<IncludeInChart={'Yes'}>}Salary)

If you search on INLINE tables and ApplyMap also you will find plenty of information regarding each of these parts of the code also.

The syntax for Set Analysis can seem a bit alien at first, but once mastered it can solve all kinds of problems for you.

Hope that helps.

Steve

Re: Excluding names from a Chart

stevedark — Fri, 07 Feb 2014 11:57:22 GMT

Just a couple of observations;

Luis, Calculated Dimensions are very inefficient. You can get away with them on small data sets but this will harm performance on larger data sets. A better way would be to create a new column in the load script that has the included names or Null for the ones you wanted to exclude - the Suppress When Value Is Null check box can then be used. If you must have a Calculated Dimension (perhaps if you do not have access to the Load Script) then Set Analysis would be more efficient than an IF statement.

Jagan, would that Set Analysis mean that any selections made on the EmployeeName would be ignored? Surely you would want it such that selections on the bars would still work?

- Steve

Re: Excluding names from a Chart

Fri, 07 Feb 2014 12:01:02 GMT

Sum({<Employee*={'Vidya','Gopal','Bhavin')>}Salary)

where I have t write this?

Re: Excluding names from a Chart

stevedark — Fri, 07 Feb 2014 12:03:38 GMT

That expression has to go in place of your current expression of sum(Salary).

What it is doing is placing a pre-defined selection over the top of what the user has selected, just for the scope of that one expression.

Re: Excluding names from a Chart

Fri, 07 Feb 2014 12:04:55 GMT

oh thank u Steve...

Re: Excluding names from a Chart

christian77 — Fri, 07 Feb 2014 12:05:54 GMT

Hi Steve.

Notice that

sum({$< Field = {'*'}-{'A'} >})

does not have the same result as

sum({$< Field - = {'A'}>})

When you notice this difference is when you filter B.

The first one wont change. It's forcing to everything.

The second one only excludes A.

Salutes.

Re: Excluding names from a Chart

richard_simpson — Fri, 07 Feb 2014 12:08:01 GMT

Hi Venu & Steve

Just remember that if you use set analysis like this, as well as hiding the unwanted names from your chart, it will also mean that if the user selects just one name in a filter (e.g. Gopal) the chart will still display salaries for all those named in the set analysis expression!

Steve's script option is a much neater solution if users are likely to filter by names...

Good luck

Richard

Re: Excluding names from a Chart

richard_simpson — Fri, 07 Feb 2014 12:10:13 GMT

Hi Christian

That is very interesting... but how does the second one exclude A? HAve you mistyped it, or am I misunderstanding something?

Thanks

Re: Excluding names from a Chart

fkeuroglian — Fri, 07 Feb 2014 12:13:55 GMT

Hi,

there is a correct way to do it but it is more dificult and there is a fast way that i give you and example

you have to put it in the dimension, have to use a calculated dimension like this

=if(Employee<>'Ram', if(Employee<>'Gopall', if(Employee<>'Vidka',Employee) ))

you have to do it with all the user you DONT WANT TO SEE in the chart.

good luck!

Fernando

Re: Excluding names from a Chart

christian77 — Fri, 07 Feb 2014 12:14:44 GMT

That's right I mistook.

I've corrected it.

Re: Excluding names from a Chart

richard_simpson — Fri, 07 Feb 2014 12:15:59 GMT

Oh, I see Thanks

Re: Excluding names from a Chart

stevedark — Fri, 07 Feb 2014 12:22:04 GMT

Hi Christian,

You will see in my expression the * before the equals symbol. This means that current selections are taken into account. Testing again I see that this is the case with the -= also (I don't think it was always). When you are specifying an include set then the * before the equals is very handy. It means you can still limit based on other selections and if there is no intersection between your selections and your set then nothing is shown.

Steve

Re: Excluding names from a Chart

christian77 — Fri, 07 Feb 2014 12:25:18 GMT

That's very useful.

I'll use it.

Thanks Steve.

Re: Excluding names from a Chart

Fri, 07 Feb 2014 13:31:36 GMT

Hi Venu

try this

=if(Match([Employee Name],'Ram','Vidya','Gopal',),sum(Salary))---this is better to use than set analysis because

we can do this by single if statement so performance is good.

Sum({<Employee*={'Vidya','Gopal','Bhavin')>}Salary)----

Both of above can be written in expression.

I hope it helps you.

regards

Mahesh T