1 2 Previous Next 15 Replies Latest reply on Nov 3, 2016 5:34 AM by Mark Bradbourne

# Filter based on 4 conditions

Hi Tableau Community,

The table below gives me for each suburb a market sentiment for May and June.

I'd like to create a filter short listing the suburbs with a market sentiment 'Neutral' for May AND a market sentiment 'Buyer' for June.

Any assistance would be appreciated.

Thanks, Julien

• ###### 1. Re: Filter based on 4 conditions

IF (Month([DATE]) = 5 and [SENTIMENT] = 'Neutral') or  (Month([DATE]) = 6 and [SENTIMENT] = 'Buyer') Then 'Yes'

Else 'No'

END

Hope this helps,

Mark

• ###### 2. Re: Filter based on 4 conditions

Thanks Mark.

Almost there.... Ideally I need the list of suburbs that meet both conditions:

Month([DATE]) = 5 and [SENTIMENT] = 'Neutral'

AND

Month([DATE]) = 6 and [SENTIMENT]= 'Buyer'

If I replace the OR by an AND in your proposed query, Tableau returns an empty list. The first condition short list the suburbs where month=5, and then the second condition (suburbs where month=6) is never met.

In advance, thanks for any assistance.

Julien

• ###### 3. Re: Filter based on 4 conditions

Try breaking it in to three parts.

1st calculation checks the first conditions. 2nd calculation checks the second conditions. Then have the 3rd calculation check to see if those calculations returned true.

That should get you where you need to be.

• ###### 4. Re: Filter based on 4 conditions

Hi Mark,

I've tried the following:

Calculated field 1:

[Sale Market Sentiment]="Neutral" and [Month]="May"

Calculated field 2:

[Sale Market Sentiment]="Buyer" and [Month]="June"

Calculated field 3:

IF([Calculation 1] or [Calculation 2]) then 'Ýes'

ELSE 'No' END

When I use calculation 3 as a filter, Tableau returns the suburbs that meet Calc 1 or Calc 2. I need both calculations to be met.

If I replace 'or' by 'and' in Calc 3, I get a null results. Reason is, Tableau short lists first all suburbs with 'Neutral' and 'May' and then none of the suburbs meet the second calculation.

I kind of need an inner joint between Calc 1 and Calc 2.

• ###### 5. Re: Filter based on 4 conditions

Can you post some sample data? I have some ideas, but I want to be sure it works.

Figured it out  think; SETS!

Create a set on each of the two calculations, then use the sets to show the members that are "IN". Give that a shot and let me know.

Thanks,

Mark

• ###### 6. Re: Filter based on 4 conditions

Hi Mark,

I've tried everything with sets and spent hours on it.

Created one set based one month=15-May-16 with one condition: MAX(market sentiment=Neutral and property type=House)

Create another set based one month=15-June-16 with one condition: MAX(market sentiment=Buyer and property type=House)

Then created a combined set with an inner join.

Didn't work!

Then I did the same based on suburbs. Didn't work again!

And I've tried many other combinations...I wouldn't ask for assistance before giving it a serious go.

Below a data sample if you've got some spare time. In advance, thank you. Julien

 Month Postcode Property Type Market Sentiment State Suburb 15-Jun-16 872 House Stressed SA AMATA 15-Jun-16 872 Unit Stressed SA AMATA 15-May-16 872 House Neutral SA AYERS RANGE SOUTH 15-Jun-16 872 House Buyer SA AYERS RANGE SOUTH 15-May-16 872 Unit Stressed SA AYERS RANGE SOUTH 15-Jun-16 872 Unit Stressed SA AYERS RANGE SOUTH 15-May-16 872 House Stressed SA DE ROSE HILL 15-Jun-16 872 House Stressed SA DE ROSE HILL 15-May-16 872 Unit Neutral SA DE ROSE HILL 15-Jun-16 872 Unit Buyer SA DE ROSE HILL 15-Jun-16 872 House Stressed SA FREGON 15-Jun-16 872 Unit Stressed SA FREGON 15-Jun-16 872 House Stressed SA KALKA 15-Jun-16 872 Unit Stressed SA KALKA 15-May-16 7000 House Neutral TAS GLEBE 15-Jun-16 7000 House Buyer TAS GLEBE 15-May-16 7000 Unit Neutral TAS GLEBE 15-Jun-16 7000 Unit Neutral TAS GLEBE 15-May-16 7000 House Buyer TAS HOBART 15-Jun-16 7000 House Buyer TAS HOBART 15-May-16 7000 Unit Neutral TAS HOBART 15-Jun-16 7000 Unit Buyer TAS HOBART 15-May-16 7000 House Buyer TAS MOUNT STUART 15-Jun-16 7000 House Buyer TAS MOUNT STUART 15-May-16 7000 Unit Buyer TAS MOUNT STUART 15-Jun-16 7000 Unit Buyer TAS MOUNT STUART
• ###### 7. Re: Filter based on 4 conditions

Is this what you are trying to filter down to ultimately?

If so, here is what I did.

• ###### 8. Re: Filter based on 4 conditions

Hi Mark,

Thanks so much for the support. I've spent so many hours on this and between you and me something that simple shouldn't be that complex... I start to be a little turned off with Tableau.

Anyway, I did exactly as you've instructed and here's what I get... Tableau returns:

Suburbs that meet May condition

And

Suburbs that meet June condition

And

Suburbs that meet May and June conditions

Ideally I just want suburbs that meet May and June condition. In the screenshot you sent you shouldn't get 'Hobart, Buyer, June 2016'.

Here's what I get when I run your proposed solution:

On that screen, only MCMINNS LAGOON should be displayed.

Cheers, Julien

• ###### 9. Re: Filter based on 4 conditions

Ideally, you would combine sets and show the intersection, but because it is derived from multiple dimensions we are unable to do so... I'm going to keep cracking at it today; don't give up!

• ###### 10. Re: Filter based on 4 conditions

Rody Zakovich - I'm stumped on this one... have you had any success with this type of issue with intersection of different dimensions? (Sorry for the call out... )

• ###### 11. Re: Filter based on 4 conditions

Mark,

Again thanks for the support. Really annoying that something so simple is so hard to execute...

Here's my latest attempt.

I've created a set based on the "Suburb' dimension with the following formula:

--

SUM(IIF([Property Type]="House",1,0))>0

AND

SUM(IIF(MONTH([Month])=5,1,0))>0

AND

SUM(IIF([Sale Market Sentiment]="Neutral",1,0))>0

--

And then, the plan was to create another set for June Buyer and combine the two sets with an inner join.

The problem with the above formula is that Tableau is looking at each query independently. Doesn't really care about the "AND" condition. Tableau seems to react more like a "OR" (see screenshot below). Maybe there is a way to combine the three queries and get the SUM > 2 ? Food for thoughts...

Thanks again, Julien

• ###### 12. Re: Filter based on 4 conditions

I think I'm getting there... yeah!!

Set on suburb with the following formula: SUM(IIF(MONTH([Month])=5,IIF([Sale Market Sentiment]="Neutral",1,0),0))>0

Get me this:

• ###### 13. Re: Filter based on 4 conditions

This might be a case where you need to reshape your data... either in the SQL query itself, doing a union of multiple queries, or something else.

You are getting close though...

• ###### 14. Re: Filter based on 4 conditions

Hi Mark,

I just wanted to say one more time thank you for your support.

I've finally cracked it! If you're interested in the solution:

1) Create one set on Suburb dimension with the following formula:

SUM(IIF([Month]=[Month Parameter 1],IIF([Sale Market Sentiment]=[Market Sentiment Parameter 1],1,0),0))>0

Note: To make it more elegant I've created two parameters to specify the month and market type.

2) Create another set on Suburb dimension with the following formula:

SUM(IIF([Month]=[Month Parameter 2],IIF([Sale Market Sentiment]=[Market Sentiment Parameter 2],1,0),0))>0

3) Combine the 2 sets with an inner join

Cheers, Julien

1 2 Previous Next