7 Replies Latest reply on Jun 8, 2016 5:08 AM by Simon Runc

# Filtering/excluding data within one dimension

Hi!

I have price data for several customers in 2015. Now the data starts to come in for 2016 and I want to compare prices between 2015 and 2016 on the customers that have both years of data.

How can I create a view only showing customers that have both years of data and excluding all other customers with only 2015 data points?

BR, Julian

• ###### 1. Re: Filtering/excluding data within one dimension

hi Julian,

If you create a FIXED LoD calculated field like this

[Order Date - Year]

YEAR([Order Date])

[Number of Years of Orders per Customer]

{FIXED [customer]: COUNTD([Order Date - Year])}

btw we can't (until Tableau 10) use derived calculations (such as YEAR) in the LoD, so we need to create a separate field first.

This will create a dimension, against every customer that is either 1 (meaning they only have orders for 1 year), or 2 (have orders for both years). You can then bring this into the filter, set to 2, and you'll only have customers that shopped both years

Hoped that does the trick, but let me know if not.

• ###### 2. Re: Filtering/excluding data within one dimension

Thanks, shoould I then convert the [Number of Years of Orders per Customer] field to discrete values before I put it on filters?

I also forgot to mention that I want to put another filter on. The view should also be able to be filter by product.

• ###### 3. Re: Filtering/excluding data within one dimension

Exactly!...what this is doing is running the COUNTD([Years]) at the customer level, and then returning this result at the customer level. So for example if Joe Bloggs shopped both years every Row with Joe Blogs as the customer will be marked by a 2 (from this field), so creates this as a 'real' dimension against the customer.

• ###### 4. Re: Filtering/excluding data within one dimension

Ok, thanks. Problem I forgot to mention is that I also want to filter by product group. That is not working right now. Any tips?

• ###### 5. Re: Filtering/excluding data within one dimension

So yes, FIXED LoDs aren't affected by 'regular' dimension filters (if you are interested in why, here is a great blog on the Tableau order of filtering operations  Vizible Difference: The Order of Operations Diagram: Update on 12/10/2015 )

however you'll notice that context filters are applied before FIXED LoDs are calculated...so if you make your product group filter in context (in the filter option you'll see 'add to context' tick this)...it will now affect your LoDs so the 2 will only bring back customer who bought in that product group in each of the 2 years. There is a performance cost to both FIXED LoDs and context filters, but unless you have a huge amount of data it should be more than fast enough.

• ###### 6. Re: Filtering/excluding data within one dimension

Thanks!

I tried to add product category to the LOD calculated field so it loooks like this:

{FIXED [customer], [product]: COUNTD([Order Date - Year])}

I think it worked thge same way or was I just lucky?

Julian

• ###### 7. Re: Filtering/excluding data within one dimension

Yes that'll work too..so in this case you are setting the FIXED to be computed at the [Customer] and [Product Group] level, and then this value is joined back at this level, so you'll now get a single assignment (1 or 2) for every [Customer]/[Product] combination (rather than every [customer] only).

...always lots of ways to do things in Tableau.