Filter expression

[m4u]

Hello

assuming I have two tables - customers and documents associated. The documents has the following fields - Document Type, Document number, Customer, Date, Sum

I would like to have a table showing the customers. Then, when customer is selected, I would like to have two linked tables (regular tables, or straigt table charts) - Invoices and Orders - basically a view of the Documents tables filtered by the Document Type field. Its like a need a way of setting a constant filter expression on each table (Document Type=Invoices, Document Type=Orders) without any relation to the user selection.

Is there an option to do that in QV? I mean, besides splitting the table into two separate tables while loading the data..

Thanks

[johnw · Accepted solution]

One simple approach would be to use a calculated dimension for your tables. Let's say you want to have the Document Number on your table. Restrict it to only appear for the correct type, then suppress when the column is null. For example:

if("Document Type"='Invoices',"Document Number")

Another common approach (if you have version 8.5 or above) is to use set analysis in the expression(s). For instance, let's say that each document had one or more line items, each of which had a "Document Quantity". Rather than making "Document Quantity" a dimension, you could make a set analysis expression like this:

sum({<"Document Type"={'Invoices'}>} "Document Quantity")

With either of those approaches, you restrict the data in that chart to that one document type. With the first approach, you only see the data in the chart if the document type is selected or allowed by your current selections. With the second approach, even if you've specifically selected ONLY orders, the Invoices table will still display because this set analysis expression overrides your selected document type for that specific chart.

There are other approaches as well, but I'm guessing those are the simplest and most common.

[m4u]

Thanks alot

[#4]

How could I expand this by another field and another value?

sum({<"Document Type"={'Invoices'}>} "Document Quantity")

E.g. I already discovered for a second field I have to do it like this:

sum({<"Document Type"={'Invoices'} AND Year={'2010'}>} "Document Quantity")

But what would I do if I want to filter on all Invoices and Order from 2010?

sum({<"Document Type"={'Invoices' OR 'Orders'} AND Year={'2010'}>} "Document Quantity")

Or do I have to do it in this way?

sum({<"Document Type"={'Invoices'} AND Year={'2010'}>} "Document Quantity") +

sum({<"Document Type"={'Orders'} AND Year={'2010'}>} "Document Quantity")

[#5]

Ok, I got it. It's just like this:

sum({<"Document Type"={'Invoices','Orders'} AND Year={'2009','2010'}>} "Document Quantity")