3 Replies Latest reply on Sep 30, 2012 6:35 PM by Alex Kerin

# Filtering Top 10 using an aggregated calculated field...

Hi,

I'm trying filter Sku's (UPC's) by the Top 10 \$ Opp. Each Sku has a value based on the following calculations:

- First, the BDI (Business Developement Index) had to be calculated using the formula below:

Formula:    ([Total Sku \$ Store] / [Total \$ Store]) / ([Total Sku \$ Terr] / [Total \$ Terr 2012])*100

- Next, the \$ Opp (Dollar Opportunity) had to be calculated using the formula below:

Formula:     if [BDI] < 100 then ((100-[BDI])*[Total Sku \$ Store]/[BDI]) else 0 end

I have tried to give the \$ Opp another value that the "Top" (Tab) within the Filter (filtering "Sku" dimension) would recognize. I have tried working with various other ranking formulas (Excel), Index(), etc., but still no luck. Tableau just doesn't acknowledge the \$ Opp values when filtering.

The \$ Opp can be sorted (Descending) if the Index() (as a Dimension) is used, but does not stick once filtered again (i.e.; Customer Filter)

Due to the nature of the numbers that I am working with I can not post my workbook.

Does anyone have any suggestions?

Thanks,

Don

• ###### 1. Re: Filtering Top 10 using an aggregated calculated field...

Hi Don,

I'm not sure I understand why the Index doesn't work after the dimension has been sorted based on \$ Opp. Would you be able to demonstrate what you mean by posting a sample workbook using superstore?

-Tracy

• ###### 2. Re: Filtering Top 10 using an aggregated calculated field...

Hi Tracy,

Here is an example using the same calculations to arrive at the \$ Opp. If you are able to show me a way to rank the \$ Opp to show the Top 10, it would be greatly appreciate.

Thanks for your help on this one.

Don

• ###### 3. Re: Filtering Top 10 using an aggregated calculated field...

A problem I've seen before - you're trying to partition one table calculation (rank) by another (\$ Opp, which has table calcs from BDI).

Not easily solved, except if the data doesn't change.

While you can't sort product name by \$ Opp in the dialog box, you can sort manually by clicking the \$ opp column header:

This changes the product sort to manual in the order of \$Opp

You then can filter on rank to show just the top 10, and adding in another filter still works.

Note that the \$ Opp for "Telescoping..." has changed - I assume because the category filter has changed the totals - not sure though. That may be a problem. If your data changes, this method will not work.