1 2 3 4 Previous Next 45 Replies Latest reply on May 12, 2017 8:02 AM by Josh Delekta

    ELSEIF Statement with Multiple Dimensions

    Josh Delekta

      Hello,

       

      I am trying to write an ELSEIF statement and finding that some categories add correctly while others don't. I've isolated some of the problem calculations and when I do they provide the correct values. I don't have a workbook to share because I can't replicate the issues with a dummy data set. Could somebody give a look and see if the coding is flawed:

       

      IF [Hierarchy] = 'Other professional' THEN "Professional"

      ELSEIF [Account] = '5030' OR [Account] = '5040' THEN "Travel"

      ELSEIF [Spend Category] = "workers" THEN "Workers"

      ELSEIF [Spend Category] = 'internal meetings' THEN 'Internal Meetings'

      ELSEIF [Spend Category] = 'events' THEN 'Internal Meetings'

      ELSEIF [Spend Category] = 'Research' THEN 'Research'

      ELSEIF [Spend Category] = 'Professional development') OR [Account] = '5110' THEN 'Professional Development & Training'

      ELSE "Other"

      END

       

      It seems like the items with multiple conditions give me the most trouble and throw off the calculation by a few thousand dollars. That said if I move the first line of 'other professional' to another line that's wrong. If I move the travel example up to the top line that gives the correct value. Any thoughts on the structure? Thank you!

        • 1. Re: ELSEIF Statement with Multiple Dimensions
          Chris McClellan

          It's always tricky to see what's going on when you're using multiple dimensions in the condition.  For your example you're using Hierarchy, Account and Spend Category dimensions, so the order might have an impact.

           

          Check your input and output though and remember that it will return the first value that is true.

           

          So in your example, if Hierarchy is 'Other professional' then the result is Professional (it doesn't evaluate any further).  If it's not 'Other professional' then check the Account values to evaluate to 'Travel'.  If it's not that, then start checking the Spend Category values.

           

          If that doesn't help, or you already know this, the only other thing I'd recommend would be to bracket the condition, so change :

           

          ELSEIF [Account] = '5030' OR [Account] = '5040' THEN "Travel"

           

          to

           

          ELSEIF ([Account] = '5030' OR [Account] = '5040') THEN "Travel"

          • 2. Re: ELSEIF Statement with Multiple Dimensions
            Josh Delekta

            Chris,

             

            Thanks for following up. I can see what you're saying about the order of

            ELSEIF statements. It appears that regardless of which way I switch the

            order it adds values to to categories I wouldn't expect. Any thoughts on a

            case statement or another approach? I'm essentially trying to create

            filtered groups that assess the whole data source.

             

            Thank you.

             

            - Josh

             

            On Tue, May 9, 2017 at 9:39 PM, Chris McClellan <

            • 3. Re: ELSEIF Statement with Multiple Dimensions
              Okechukwu Ossai

              Josh,

               

              You need to use nested IF statements. It will be difficult to offer a good solution without seeing how your data is structured, at least the hierarchy relationship between the dimensions in the formula may provide a little insight to where the problem might coming from.

               

              Ossai

              • 4. Re: ELSEIF Statement with Multiple Dimensions
                Josh Delekta

                Ossai,

                 

                Thank you for your response. The underlying data is roughly 400,000 rows

                and contains just about every value imaginable from the data source its

                being pulled so its not easily shareable or changed to fake the data. Does

                it help to provide the order in which the columns appear in the source?

                 

                1. Account

                2. Spend Category

                3. Hierarchy

                 

                I took the previous advice and tried structuring it slightly differently

                based on that order and it worked successfuly for 6 out of 9 groups. Any

                thoughts would be greatly appreciated. Thank you.

                 

                On Tue, May 9, 2017 at 10:25 PM, Okechukwu Ossai <

                • 5. Re: ELSEIF Statement with Multiple Dimensions
                  Chris McClellan

                  With the logic you posted, this is the results.

                   

                  Even with 400,000 records, it would be good to see your Account, Spend Category, Hierarchy combinations ONLY (ie just those 3 columns) and the results that are right/wrong.

                  • 6. Re: ELSEIF Statement with Multiple Dimensions
                    Josh Delekta

                    Chris,

                     

                    Please see attached and thanks for your help so far. It's not the quantity of data but the type. I've tried my best to replicate what is going on and I think this does the trick:

                     

                    In the calculation: 'Professional Services' equals $755,075

                    In the spreadsheet: 'Services' under 'Hierarchy' equals $756,175

                     

                    If you isolate the calculation from the ELSEIF then 'Professional Services' is correct. This is what I'm running into. Any thoughts?

                     

                    Thank you.

                    • 7. Re: ELSEIF Statement with Multiple Dimensions
                      Anna Cheetham

                      If you create a quick worksheet with the values of Account, Professional Services, and Calculation1, filtered only to those records where Professional Services = 'Professional Services' and Calculation1 does not, you can see what is happening:

                       

                       

                      You can see that all the records where Calculation1 is not Professional Services have one of 3 different Account values.  All of these values of Account are specified within Calculation1 above where you're classifying things into Professional Services:

                       

                       

                      So the question is, which of these should take priority?  If Account=5040 but Hierarchy = 'Professional Services', should Calculation1 = Travel, or Professional Services?

                       

                      Regards

                       

                      Anna

                      • 8. Re: ELSEIF Statement with Multiple Dimensions
                        Okechukwu Ossai

                        Josh,

                         

                        In your database there are some accounts which are 5130 but classified as Travel and Professional Services. Your formula assigns all 5130 account as Information Technology. Is this what you want to do?

                         

                        Ossai

                        • 9. Re: ELSEIF Statement with Multiple Dimensions
                          Josh Delekta

                          Anna,

                           

                          I'm not sure I follow. Are you saying that based on which account number I choose to take priority that something else will always be falling out because the calculation assesses on a row level basis? Is there a way to isolate each so Tableau assesses the whole data set over and over?

                           

                          Thank you. I appreciate it!

                          • 10. Re: ELSEIF Statement with Multiple Dimensions
                            Josh Delekta

                            Ossai,

                             

                            Yes that's fine. I had to scrub alot of the detail to share this data so I

                            know some of it might not make sense of context. Is there a way to isolate

                            each grouping so the calculation reads 1 line assess each row in the data

                            source goes to the next and assesses each row in the data source? Is LOD a

                            possibility here?

                             

                            Thank you!

                             

                            On Wed, May 10, 2017 at 10:00 AM, Okechukwu Ossai <

                            • 11. Re: ELSEIF Statement with Multiple Dimensions
                              Okechukwu Ossai

                              Josh,

                               

                              Using your example below.

                               

                              Services is 756,175 from the spreadsheet. However, there are few inconsistencies in Calculation1. You have multiple assignation for some of the accounts. For example account 5040, 5110 and 5130 have been assigned to Travel, IT and Professional Services. These accounts cannot live in more than one category at the same time, that is why Calculation1 is returning incorrect result.

                              • 12. Re: ELSEIF Statement with Multiple Dimensions
                                Anna Cheetham

                                Hi Josh

                                 

                                Yes, the calculation is working at row level.  So because you're using ELSEIFs, for each record, the calculation will work through all of your ELSEIF conditions in order until it hits one that is true, and use that one to assign the value.

                                 

                                So, for example, for the records where Account=5040 and Hierarchy=Services, the calculation will go something like:

                                 

                                IF [Account] = '5030' THEN 'Travel' Does Account=5030?  No, so let's carry on...

                                ELSEIF [Account] = '5040' THEN 'Travel'  Does Account=5040?  Yes it does, so the value is Travel.  Stop here.

                                 

                                The rest of the calculation will be completely ignored, because it's found a condition that is true.

                                 

                                You can see how the formula is evaluating if you do something like this:

                                 

                                • 13. Re: ELSEIF Statement with Multiple Dimensions
                                  Josh Delekta

                                  Thank you both  for these explanations. Are you basically saying there is no way to go the desire result? Any suggestions on a different approach?

                                  • 14. Re: ELSEIF Statement with Multiple Dimensions
                                    Okechukwu Ossai

                                    Notice that Services is now calculating correctly if i delete the lines of code where Account = 5040, 5110 and 5130 were defined explicitly

                                     

                                     

                                    All you need is a consistent way of defining your new category. If you write down your requirements in words then I can get you the code.

                                    1 2 3 4 Previous Next