12 Replies Latest reply on Jul 1, 2013 11:08 PM by Steve Martin

    Translating Excel formulas - IF AND

    georgia.nichols

      Morning All,

       

      Possibly a really simply question for some, but coming from a rookie Tableau user, I'm hitting walls trying to figure it out!

      I'm trying to create a calculated field which creates alters the value in [LP] if the condition is true.

       

      My normal formula, in access or something, would look like this ...

      if(and([LP'='RV',[Component]="MBROOK'), "RB",[LP])

       

      The following code is 'valid' in Tableau, but when I add the calcuated field pill to my row data, I get 'Null' in all rows.

      if([LP]='RV' and ([Component]='MBROOK')) THEN 'RB' ELSE [LP] END

       

      What I want, essentially, is to set the LP to RB where the LP is RV and Component equals MBROOK, and leave all other LP's as normal if they dion't equal RV.

       

      Thanks in advance!

      Georgia

        • 1. Re: Translating Excel formulas - IF AND
          Alex Kerin

          Capitilization? Do LP and component have values as expected?

          • 2. Re: Re: Translating Excel formulas - IF AND
            Toby Erkson

            I agree with Alex.  Try this:

            if(UPPER([LP])='RV' and (UPPER([Component])='MBROOK')) THEN 'RB' ELSE [LP] END
            
            • 3. Re: Re: Translating Excel formulas - IF AND
              georgia.nichols

              Thanks for your help with this, on reflection, I had a blonde moment and wasn't linking across to the right corresponding field.

               

              However, my next problem ...

               

              My code is currently set to ...

               

              if([LP]='RV' and ([Component]='M BROOK' OR [Component]='NCIG Type 1')) THEN 'RB'

              ELSEIF([LP]='BF' and ([Component]<>'BFLD SP' OR [Component]<>'BFLD STM' OR [Component]<>'BFLD STR' OR [Component]<>'RATHLUBA' OR [Component]<>'BFLD TSS' OR [Component]<>'BFLD TTH'))

              Then 'DS'

              ELSEIF([LP]='BF' and ([Component]='BFLD SP' OR [Component]='BFLD STM' OR [Component]='BFLD STR' OR [Component]='RATHLUBA' OR [Component]='BFLD TSS' OR [Component]='BFLD TTH'))

              Then 'BF'

              Else [LP] END

               

              For some reason, I had to add in the second section to actually include the 'BF' data, however, when the results come back, BF still has a 'Null' next to itself. Why is it simply not showing 'BF' and returning values??

               

              tabPic.jpg

              • 4. Re: Re: Re: Translating Excel formulas - IF AND
                Toby Erkson

                A packaged workbook to look at would help but this is getting beyond my developer skills.  I would recommend trying the IN() function (and maybe NOT IN() too) instead of the list of ORs.

                ...ELSEIF([LP]='BF' and [Component] IN('BFLD SP', 'BFLD STM', 'BFLD STR', 'RATHLUBA', 'BFLD TSS', 'BFLD TTH'))
                
                1 of 1 people found this helpful
                • 5. Re: Re: Re: Translating Excel formulas - IF AND
                  georgia.nichols

                  Hi Toby,

                   

                  Thanks for that, I hadn't reaslied you could do In and Not In functions in Tableau.

                  I'll try that this morning and see how I go!

                   

                  Regards,

                  Georgia

                  • 6. Re: Re: Re: Translating Excel formulas - IF AND
                    georgia.nichols

                    Thanks to Alex Kerin and Toby Erkson for your assistance - turns out I

                    - can't use the In and Not In functions in Tableau

                    - have to use all listed components, separated by 'OR' ... to distinguish between one LP and another.
                    very clunky, but solution achieved!

                    • 7. Re: Re: Re: Translating Excel formulas - IF AND
                      Toby Erkson

                      The IN() function didn't work?  That's odd because it's a valid function.  Hmm.  I'd have to play with that one myself...sorry it didn't work out for you   Glad you got if fixed, though.

                      • 8. Re: Re: Re: Translating Excel formulas - IF AND
                        georgia.nichols

                        Unfortunately not

                        I coudln't find it in my available functions list, and I kept getting an error around parenthesis at character 90 (i.e at the 'IN'), so the formula wouldn't validate. I'm using Tableau 8 if that helps.

                        • 9. Re: Translating Excel formulas - IF AND
                          georgia.nichols

                          Ok, I'm having a nother little issue with IF statements, which I can't resolve for love nor money!

                          The following code is only showing answers for options which relate to 'All Achieved'.

                          Any suggestions ...

                           

                          if([BudgetOutcome]="Budget Incomplete" and([OrderOutcome]="Order Incomplete")) then "All Unachieved"

                          ElseIf([BudgetOutcome]="Budget Complete" and ([OrderOutcome]="Order Incomplete")) then "Budget Achieved"

                          ElseIf([BudgetOutcome]="Budget Complete" and ([OrderOutcome]="Order Complete")) then "All Achieved"

                          ElseIf([BudgetOutcome]="Budget Incomplete" and ([OrderOutcome]="Order Complete")) then "Order Achieved"

                          Else "" end

                          • 10. Re: Re: Translating Excel formulas - IF AND
                            Steve Martin

                            Hi Georgia,

                             

                            Apologies for the really vague response to your op but have you tried using the raw_sql functions? - I haven't used these since Tableau 6 but they may allow you to query the workbook in the way that you want.

                             

                            On your second question I had no issues though I did adapt the code to the following:

                             

                            if [BudgetOutcome]="Budget Incomplete" and [OrderOutcome]="Order Incomplete" then "All Unachieved"

                            ElseIf [BudgetOutcome]="Budget Complete" and [OrderOutcome]="Order Incomplete" then "Budget Achieved"

                            ElseIf [BudgetOutcome]="Budget Complete" and [OrderOutcome]="Order Complete" then "All Achieved"

                            ElseIf [BudgetOutcome]="Budget Incomplete" and [OrderOutcome]="Order Complete" then "Order Achieved"

                            Else Null end

                             

                            Edit: you could use string type or number in the final else, I just chose to use Null

                            • 11. Re: Re: Translating Excel formulas - IF AND
                              georgia.nichols

                              Hi Steve,

                               

                              I'm not sure what my issue is, but that code simply replaced my blanks with Nulls - which I expected.

                              Unfortuantely, I'm still experiencing the issue regardless of bracketing.

                              I haven't tried the raw-sql functions, I must look into these though. Thanks for the tip!

                               

                              Cheers,

                              Georgia


                              • 12. Re: Translating Excel formulas - IF AND
                                Steve Martin

                                Hi Georgia,

                                 

                                I can't see any reason why the code should not work though can I make another couple of suggestions:

                                 

                                • Have you checked you source data to ensure the spelling and grammar are exactly as the calculation
                                • Try creating a new workbook against the data-source to check if the issue is something to do with the existing workbook (I've had this on a number of occassions)
                                • Try opening a new workbook an connecting to Superstore Sales then trying all of your calcs against this data - we all know this and you can then post additional issues using data that we can easily access and manipulate whilst you can try to achieve your intentions with a 100% stable data-source