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

    Filter based on 4 conditions

    Julien Mougenot

      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
          Mark Bradbourne

          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
            Julien Mougenot

            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
              Mark Bradbourne

              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
                Julien Mougenot

                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
                  Mark Bradbourne

                  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
                    Julien Mougenot

                    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

                     

                       

                    MonthPostcodeProperty TypeMarket SentimentStateSuburb
                    15-Jun-16872HouseStressedSAAMATA
                    15-Jun-16872UnitStressedSAAMATA
                    15-May-16872HouseNeutralSAAYERS RANGE SOUTH
                    15-Jun-16872HouseBuyerSAAYERS RANGE SOUTH
                    15-May-16872UnitStressedSAAYERS RANGE SOUTH
                    15-Jun-16872UnitStressedSAAYERS RANGE SOUTH
                    15-May-16872HouseStressedSADE ROSE HILL
                    15-Jun-16872HouseStressedSADE ROSE HILL
                    15-May-16872UnitNeutralSADE ROSE HILL
                    15-Jun-16872UnitBuyerSADE ROSE HILL
                    15-Jun-16872HouseStressedSAFREGON
                    15-Jun-16872UnitStressedSAFREGON
                    15-Jun-16872HouseStressedSAKALKA
                    15-Jun-16872UnitStressedSAKALKA
                    15-May-167000HouseNeutralTASGLEBE
                    15-Jun-167000HouseBuyerTASGLEBE
                    15-May-167000UnitNeutralTASGLEBE
                    15-Jun-167000UnitNeutralTASGLEBE
                    15-May-167000HouseBuyerTASHOBART
                    15-Jun-167000HouseBuyerTASHOBART
                    15-May-167000UnitNeutralTASHOBART
                    15-Jun-167000UnitBuyerTASHOBART
                    15-May-167000HouseBuyerTASMOUNT STUART
                    15-Jun-167000HouseBuyerTASMOUNT STUART
                    15-May-167000UnitBuyerTASMOUNT STUART
                    15-Jun-167000UnitBuyerTASMOUNT STUART
                    • 7. Re: Filter based on 4 conditions
                      Mark Bradbourne

                      JulienHelp.png

                       

                      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
                        Julien Mougenot

                        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
                          Mark Bradbourne

                          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
                            Mark Bradbourne

                            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
                              Julien Mougenot

                              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
                                Julien Mougenot

                                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
                                  Mark Bradbourne

                                  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
                                    Julien Mougenot

                                    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