IF (Month([DATE]) = 5 and [SENTIMENT] = 'Neutral') or (Month([DATE]) = 6 and [SENTIMENT] = 'Buyer') Then 'Yes'
Hope this helps,
Almost there.... Ideally I need the list of suburbs that meet both conditions:
Month([DATE]) = 5 and [SENTIMENT] = 'Neutral'
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.
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.
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.
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.
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.
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
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
Suburbs that meet June condition
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.
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!
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([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
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...
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