6 Replies Latest reply on Sep 30, 2016 4:05 PM by Shinichiro Murakami

# Filtered view on top of LOD without having the Dimension as a detail

I'm struggling to achieve the following.

I'm using LOD to count customers at the level of "customer name" and "country". This works fine and shouldn't change.

However, I now want to create a filtered view over the data without filtering out the underlying data.

I believe the way to do this is by using Table Calculations (since they don't filter out any data from the dashboard).

When I do that, I can't get a sum, but need to add the dimensions as a detail in the marks view. As a result it breaks-out my number into multiple numbers in the view, which is not what I want.

Could someone have a look at the attached workbook and help me out? I'm pretty sure this must be pretty straight forward. The real workbook is much more complex but the base principle / goal is the same:

- Unique Customer count (customer/country) = 5. This number always stays the same. When not applying filters, I just want to show '5'.

- When filtering on Region 'USA', I just want to show '3'.

- When filtering on segment 'Medium', I just want to show '2'

- etc.

Thanks!

• ###### 1. Re: Filtered view on top of LOD without having the Dimension as a detail

I'm not sure I understand your request correctly, but does this meet you needs?

[Country x Customer]

[Country] +" "+ [Customer]

Thanks,

Shin

• ###### 2. Re: Filtered view on top of LOD without having the Dimension as a detail

Hi Shin, thanks. And no it doesn't...but I get the confusion.

There are a bunch of checks as part of the LOD calculation that determine if a record should count or not. I have changed the spreadsheet to include some zero lines.

For example, when you filter on Segment 'Large' the number shown should be 3, not 1. They way you do it, you filter out any non-Large segment, while the customer qualifies across all segments.

For example: when you filter on Sales Region "UK", the number shown should be 3, not 4. You are including the customer in France, but that one doesn't qualify the aggregate check for the total of customer/country combination.

So:

1 - the LOD check accummulates the total for the customer/country combination.

2 - the filters for Sales Region and Segment pick out those customer/country combinations (as an accumulate; not as a filtered dataset) that meet the check from point 1 but also have at least one line with the corresponding Region or Segment set.

• ###### 3. Re: Filtered view on top of LOD without having the Dimension as a detail

Note that I though about FIXED LOD calcs, but that is not an option since I still DO want to filter the underlying dataset for some valid purposes (like filtering the total by product-set).

• ###### 4. Re: Filtered view on top of LOD without having the Dimension as a detail

I don't understand the whole story yet, but just one note.

You can change the filter type to "Context Filter", then Context filter can work BEFORE LOD calc.

thanks,

Shin

• ###### 5. Re: Filtered view on top of LOD without having the Dimension as a detail

Yes I know. But the filtering is not the issue. I don't want to filter the data, I just want to filter what I see (but not exclude data from the dataset which changes the outcome of the lod calc)

Op 29 sep. 2016 om 20:44 heeft Shinichiro Murakami <tableaucommunity@tableau.com<mailto:tableaucommunity@tableau.com>> het volgende geschreven:

Filtered view on top of LOD without having the Dimension as a detail

• ###### 6. Re: Filtered view on top of LOD without having the Dimension as a detail

Still no sure I meet your request, but please take a look.

[Fixed LOD]

{fixed:countd(if {fixed [Unique Customer]:sum([Sales])}>0 then [Unique Customer] end)}

Thanks,

Shin