9 Replies Latest reply on Nov 21, 2016 8:27 AM by Simon Runc

    Do multiple calculations in a single CASE statement match.

    Zachary Robinson

      Hi Community.  Third question in as many days.  I'm starting to become a regular!

       

      Here's what I've got.  I have a case statement determining Year, Quarter, or Month (figured that out by finding a thread here), and I need some parts of my calculation to rely on that as well.  Take a look at the viz, then my calculation:

       

      Tableau3.jpg

       

      --- Working capital works fine for "Years" or "Months" in the Time Period Selection, but I'm having trouble with Quarters.  Here is my calculated field code:

       

       

      CASE [Time Period Selection]

          WHEN "Year" THEN

       

       

              (SUM((IF MONTH([Period]) = 12 AND [Accounts] = "Accounts Receivables" THEN [Values] END)))

       

       

              +

       

       

              (SUM((IF MONTH([Period]) = 12 AND [Accounts] = "Inventory" THEN [Values] END)))

       

       

              -

       

       

              (SUM((IF MONTH([Period]) = 12 AND [Accounts] = "Accounts Payable" THEN [Values] END)))

       

       

          WHEN "Month" THEN

       

       

              SUM(IF [Accounts] = "Accounts Receivables" THEN [Values] END)

       

       

              +

       

       

              SUM(IF [Accounts] = "Inventory" THEN [Values] END)

       

       

              -

       

       

              SUM(IF [Accounts] = "Accounts Payable" THEN [Values] END)

       

       

          WHEN "Quarter" THEN

       

       

              (SUM((IF MONTH([Period]) = 3 AND [Accounts] = "Accounts Receivables" THEN [Values] END)))

       

       

              +

       

       

              (SUM((IF MONTH([Period]) = 3 AND [Accounts] = "Inventory" THEN [Values] END)))

       

       

              -

       

       

              (SUM((IF MONTH([Period]) = 3 AND [Accounts] = "Accounts Payable" THEN [Values] END)))

       

       

          WHEN "Quarter" THEN //I know this is bogus and doesn't work, as per my viz results.

       

       

              (SUM((IF MONTH([Period]) = 6 AND [Accounts] = "Accounts Receivables" THEN [Values] END)))

       

       

              +

       

       

              (SUM((IF MONTH([Period]) = 6 AND [Accounts] = "Inventory" THEN [Values] END)))

       

       

              -

       

       

              (SUM((IF MONTH([Period]) = 6 AND [Accounts] = "Accounts Payable" THEN [Values] END)))

       

       

       

       

      END

      If I remove the IF MONTH([Period]) = 3 and use the IF MONTH([Period]) = 6, it does populate Q2 appropriately on the viz. 

       

      So, Community, how do I get the "Quarter" case statement to use all 4 of my quarterly calculations?  Where is a good old for() loop when you need one?

        • 1. Re: Do multiple calculations in a single CASE statement match.
          Norbert Maijoor

          Hi Zachary,

           

          Find my approach as reference below based on sheet-selector and attached workbook version 9.3

           

          In principle ""splitting" your solution in two parts. In each sheet you store your  calculation and solving your Year, Quarter, Month "issue" by using 3 different sheets which  you can select based on a parameter.

           

           

          2 of 2 people found this helpful
          • 2. Re: Do multiple calculations in a single CASE statement match.
            Zachary Robinson

            Thanks Norbert.  I really appreciate the above example, and will certainly find it useful in the future.  However, I would like to leave the question open a bit longer to see if there is a way to do this within the case statement.  Your solution is elegant, but I feel like my problem can be solved in an even less complicated way.

             

            Thanks!

            • 3. Re: Do multiple calculations in a single CASE statement match.
              Norbert Maijoor

              Hi Zachary,

               

              Thanks for the feedback. Would like to ask one of the "seniors" on this forum for guidance.

              Simon Runc Could you "shine your light" and this one. Upfront. Thanks for your time & attention.

              • 4. Re: Do multiple calculations in a single CASE statement match.
                Simon Runc

                hi Zachary,

                 

                I've only taken a quick look...and the fact you get a result when you change the Month = 6, leads me to guess you might just need to wrap each of the 3 calculation in a ZN (Use Zero if NULL).

                 

                When Tableau encounters NULLs in a single field, it just doesn't use them, but when used across fields (as here) if one result equates to a NULL, the entire addition is NULL (same for string concatenations)...So 4 + 4 + NULL = NULL

                 

                So change your calc's like this

                 

                        ZN((SUM((IF MONTH([Period]) = 3 AND [Accounts] = "Accounts Receivables" THEN [Values] END))))

                        +

                       ZN( (SUM((IF MONTH([Period]) = 3 AND [Accounts] = "Inventory" THEN [Values] END))))

                        -

                       ZN( (SUM((IF MONTH([Period]) = 3 AND [Accounts] = "Accounts Payable" THEN [Values] END))))

                 

                value is NULL it will just get SUMed as Zero.

                 

                If that's not it, can you mock up an example using Superstore, and I'll be happy to take a look.

                2 of 2 people found this helpful
                • 5. Re: Do multiple calculations in a single CASE statement match.
                  Norbert Maijoor

                  HI Simon,

                   

                  Thanks for prompt action. Much appreciated

                  • 6. Re: Do multiple calculations in a single CASE statement match.
                    Zachary Robinson

                    Simon,

                     

                    I'm not sure you understood the problem I'm having, so I made a superstore mock-up.  Basically, My calculations work without a problem.  The only issue I have is getting the quarter calculation to iterate multiple times within the same CASE statement match.  e.g.

                     

                    WHEN "Quarter"

                     

                    THEN

                    CALC ONE (month = 3)

                     

                    AND

                     

                    CALC TWO (month = 6)

                     

                    AND

                     

                    CALC THREE (month = 9)

                     

                    AND

                     

                    CALC FOUR (month = 12)

                     

                    END

                     

                    It's the AND statement (which doesn't exist within CASE function) that I'm having a hard time with.  Basically, I need it to display all 4 quarters, and I need the calculation to be specific to the month specific to that quarter (3, 6, 9, 12). I'm guessing I need a nested CASE statement or some other function I'm not aware of, but I haven't figured out what that is.

                     

                    Take a look at sheet 21 in the attached twbx.

                     

                    Thanks to both of you (and anyone else that wants to jump in) for your help!

                    • 7. Re: Do multiple calculations in a single CASE statement match.
                      Simon Runc

                      Yes I get it now...as I said, I only had a quick look (...obviously too quick!!).

                       

                      So yes the issue with the case statement (and IF THEN ELSE for that matter) is that the formula exits, once a condition is true...so never gets to the 6 Month test, as the "does parameter = 'quarter'?" is already satisfied in the 3 month test.

                       

                      So to do this we need to add an OR to the 3-month test for each of the 6,9,12) in the same test. So the final test becomes (notice the extra brackets around the OR statements...this is so the are equated together (if any of these conditions is met) along with the AND test on Segment.

                       

                      WHEN "Quarter" THEN

                      ZN((SUM((IF (MONTH([Order Date]) = 3 OR MONTH([Order Date]) = 6 OR MONTH([Order Date]) = 9 OR MONTH([Order Date]) = 12) AND [Segment] = "Home Office" THEN [Sales] END))))

                      +

                      ZN((SUM((IF (MONTH([Order Date]) = 3 OR MONTH([Order Date]) = 6 OR MONTH([Order Date]) = 9 OR MONTH([Order Date]) = 12) AND [Segment] = "Consumer" THEN [Sales] END))))

                      -

                      ZN((SUM((IF (MONTH([Order Date]) = 3 OR MONTH([Order Date]) = 6 OR MONTH([Order Date]) = 9 OR MONTH([Order Date]) = 12) AND [Segment] = "Corporate" THEN [Sales] END))))

                       

                      Hope that does the job!...let me know if not

                      2 of 2 people found this helpful
                      • 8. Re: Do multiple calculations in a single CASE statement match.
                        Zachary Robinson

                        That did it exactly.  Thanks so much!  I thought the OR operator in the IF statement would only return the first match it found.  Learn something new every day!

                         

                        Thanks again Simon Runc and Norbert Maijoor, You guys really helped me solve this one and gave me tips for the future!

                        • 9. Re: Do multiple calculations in a single CASE statement match.
                          Simon Runc

                          Glad it did the trick...so the key is the brackets and the OR

                           

                          (Is this true? OR is this true? OR is this true?) AND (is this true?)....the 3 OR statements are taken together and only one needs to be True for everything in the bracket to return True, and then we just have

                          (Is this True?) AND (is this True?)

                           

                          There is actually an order of operations to this (like BODMAS in arithmetic)...but I can never remember the precedence, so just bracketing stuff ensure everything gets evaluated in the correct order/grouping

                          1 of 1 people found this helpful