Solved: Excluding names from a Chart - Qlik Community

Report Inappropriate Content · ‎2014-02-07

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

Report Inappropriate Content · ‎2014-02-07

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

View solution in original post

luis_pimentel · ‎2014-02-07

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.

jagan · ‎2014-02-07

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.

luis_pimentel · ‎2014-02-07

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,

stevedark · ‎2014-02-07

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

stevedark · ‎2014-02-07

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

Report Inappropriate Content · ‎2014-02-07

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

where I have t write this?

stevedark · ‎2014-02-07

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.

Report Inappropriate Content · ‎2014-02-07

oh thank u Steve...

christian77 · ‎2014-02-07

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.