6 Replies Latest reply on Jan 18, 2016 5:10 AM by Tableau kumar

# How to use aggregate filed as filter

IF Sum([Amount LCY])>= 0 and Sum([Amount LCY])<=10000 THEN"0-10000"

ELSEIF Sum([Amount LCY])>=10001 and Sum([Amount LCY]) <=30000 THEN  "10001-30000"

ELSEIF Sum([Amount LCY]) >=30001 and Sum([Amount LCY]) <=50000 THEN  "30001-50000"

ELSEIF Sum([Amount LCY]) >=50001 and Sum([Amount LCY]) <=100000 THEN  "50001-100000"

ELSEIF Sum([Amount LCY]) >=100001 and Sum([Amount LCY]) <=500000 THEN  "100001-500000"

ELSEIF Sum([Amount LCY]) >=500001 and Sum([Amount LCY]) <=1000000 THEN  "500001-100000"

ELSEIF Sum([Amount LCY]) >=1000001 and Sum([Amount LCY]) <=5000000 THEN  "1000001-5000000"

ELSEIF Sum([Amount LCY]) >=5000001 and Sum([Amount LCY]) <=7500000 THEN  "5000001-7500000"

ELSEIF Sum([Amount LCY]) >=7500001 and Sum([Amount LCY]) <=10000000 THEN  "7500000-10000000"

ELSEIF    Sum([Amount LCY]) >=10000001 AND  Sum([Amount LCY])<=100000000001  THEN "Above 1Cr"

else "Chk"

END

i am trying to use this as filter but not getting the option might be because of  aggregation.

• ###### 1. Re: How to use aggregate filed as filter

This from yesterday was a similar issue to yours, solved by using a parameter and a calculated field referencing the parameter as the filter field: Create Custom Date Filter

• ###### 2. Re: How to use aggregate filed as filter

Hi Shankar

Is the field [Amount LCY] already a calculation, if so, what is it?

I just quickly wrote the below to test, open up Superstore and try it, it works fine as a filter

IF SUM([Sales])>=0 and SUM([Sales])<1001 THEN '0-1000'

ELSEIF SUM([Sales])>=1001 and SUM([Sales])<10000 THEN '1001-10000'

ELSEIF SUM([Sales])>=10001 THEN '10001+'

ELSE NULL

END

I suspect your [Amount LCY] already contains a SUM(), if so, try remove it.

Cheers

Mark

• ###### 3. Re: How to use aggregate filed as filter

I suspect that @Mark is on Tableau 9.2 and @Shankar is on an earlier version.

The calculation is a discrete regular aggregate. Tableau v9.1 and earlier do *not* support discrete regular aggregates on the Filters Shelf, when we try to drag one over it doesn't work. The workarounds in that case are one of the following:

a) use a table calculation (wrap the whole thing in LOOKUP([your calc],0). Discrete table calculation results can be placed on the Filters Shelf.

b) if you're on v9.0 or higher use a FIXED level of detail expression for the calc. The result can then be a discrete dimension that can be placed on the Filters Shelf.

c) change the results so that they are numbers that can be used as a continuous regular aggregate (like 1,2,3,4) because those can be placed on the Filters Shelf.

Tableau v9.2 added support for filtering by discrete regular aggregates, see http://drawingwithnumbers.artisart.org/feature-geek-filtering-by-discrete-regular-aggregate-pills-in-v9-2/ for details.

Jonathan

2 of 2 people found this helpful
• ###### 4. Re: How to use aggregate filed as filter

Hi Jonathan,

Hope you are doing Good,

I was searching for help in https://community.tableau.com/thread/156233 tableau forum and came across this workbook that exactly help in my task. But I have a small question for you, In this attached workbook(this workbook is been posted by you in one of the replies to the question on forums).

Question: You place a filter on measure name in order to change the view to table but now i want to create the same step for dimensions where i have to select multi dimensions and multiple measures. I got selecting multiple measures from your workbook. Can you help me in selecting multiple dimensions to make users chose their own dim and measures in order to view the table.

Thank you

• ###### 5. Re: How to use aggregate filed as filter

Please re-read the second paragraph in Re: field/column label when using parameter control. This is not easy to set up in a Tableau dashboard, nor it is necessarily advisable. If you really have to go there then there are three options:

1. Use multiple parameters as described elsewhere in that thread.

2. Use a single parameter to set up pre-defined combinations as in Dynamic Hierarchies with Tableau - Clearly and Simply.

3. Give your users an analytics-ready data source and either a) turn on web-editing capabilities or b) give them Tableau Desktop so they can do their own drag & drop of whatever dimensions they want.

Since Tableau is a tool for supporting the cycle of visual analysis #3 can give users a lot more than some pre-defined dashboard that offers picking from a few options in limited ways.

Jonathan