Filtering in table

[#1]

I am brand new to qlikview and creating my first project in it.  I've imported the data into a table and i would like to filter the table to only show only the values where Area = Inventory.  How would i go about doing this?

Thanks

[datanibbler]

Hi,

have a look on youtube, there is plenty of material to help you get along in QlikView.

I could recommend "QlikView 11 for Developers", a very worthwhile book. A bit old, judging by the rate at which QlikView has developed, but still an excellent start.

To answer your question - just right-click on an empty space on the screen, select "add fields", select any field from any table - on which you want to filter - make it available on the screen and you can click on any value in that field - the records (that is, all other fields) from the table you have loaded will be filtered to show only the records that match your selection.

[oscar_ortiz]

For a Table object you can simply add a list box for your field Area and select Inventory from the list box to filter your data.

For a Straight Table Chart Object you can add Area as a Dimension and create an Expression using the Area as part of the expression:

Sum( {< Area={'Inventory'} >} Sales )

Good luck

Oscar

[JonnyPoole]

To filter a table box you would have to add a list box (using the field Area) and select Inventory.

But in a chart / straight table, which represents aggregated records in tabular format you can use 2 common ways.

SET ANALYSIS

In your expression(s) , alter the existing expression to include a SET expressions

change:  sum(Sales)  ->  Sum( {< Area={'Inventory'} >} Sales )   (as mentioned above)

Note that in charts, you should be using aggregation functions to summarize data (max, min , avg, sum etc..)

CONDITIONAL Statements

Use an IF() that will only calculate the expression based on a condition

change: sum(Sales)  ->   sum ( if ( Area='Inventory', Sales))

There are others but those are widely used and common.

[#5]

I think i messed up when i did my original description.  The field "Area" is actually "Department"... which means that all these fields are text and not numerical.  I would like to display the departments that are labeled inventory in my table.

Thanks for the help so far!

[JonnyPoole]

Its the same approach regardless of data type...   do you want to share/post your QVW ?  I think sharing an example will work best.

[#7]

Im starting to understand the concept.  So if i do the expression under the 'Dep' field on the table...

sum( if ( Dep='Inventory', sales))

What does the 'Sales' part represent?  That is the only part i am unsure about.

[JonnyPoole]

"Sales" refers to a numeric field in your data set.

2 steps back... you mentioned that you would like to filter a 'table' by Dep='Inventory'.

In the QlikView UI design, there is no 'table' per se, but there is a table box (to show detail records) and more commonly used, there are also charts with a 'straight table' data type. there is also a pivot table data type.

Table boxes do NOT support expressions so neither technique will work above to filter a set of records in a table box. You would have to filter the application by say, adding a list box that shows the Dep field, and select the 'inventory' value (which would filter all objects on the screen including the table box).

Charts do support expressions, in fact you MUST include at least 1 expression in a chart. Furthermore, when you add an expression  (count of something, total of something etc...) you normally would use a chart aggregation function like sum() , count() etc... to do the aggregation.

So when using a straight table chart or pivot table chart, you can use the above techniques to filter the chart permanently with Dep='Inventory'.

If you could explain your need in terms of what you want to show i could offer some more guidance on whether you need a chart, how you would write the expression etc...

[#9]

Thank you that has helped me a lot!  Basically i am making a qlikview app that has several different sheets and it will display the errors in each department (inventory, quality, etc). I need each department on an individual sheet.  Is there a way that I can have the list box automatically select a particular department when the individual tab is selected?  My biggest problem is that every time i filter the table it is changed on every indivual sheet... it doesnt stay static on that individual sheet even though each table has its own unique object ID in each sheet.

[JonnyPoole · Accepted solution]

You can do that, in the sheet properties you can apply a 'trigger' .  A trigger is an 'action' that is applied on a specific event like 'activate sheet'.

You can add an action to do a 'select in field'  on the Dep field and pass in a filter like   ='Inventory'

If you don't want users playing with the selection you can add a 2nd action to the trigger to 'lock' the field.

If you do this on every sheet, you will need to do an 'unlock field' action first

Trigger on each sheet

1. Unlock Field   (Dep)

2. Select in Field  (Dep)

3. Lock Field (Dep)

This will apply the filter to the whole application , except that it automatically shifts the filter with every sheet change.

You can do this or lock in filter at the object level (so even if a user changes the Dep selection a chart etc.. will not budge ).

If you take your current approach, consider a summary dashboard sheet  that compares all Departtments with buttons or other methods to 'drill' to each sheet.