
1. Re: Do multiple calculations in a single CASE statement match.
Norbert Maijoor Nov 19, 2016 12:00 AM (in response to Zachary Robinson)Hi Zachary,
Find my approach as reference below based on sheetselector 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.

select sheet.twbx 1.2 MB


2. Re: Do multiple calculations in a single CASE statement match.
Zachary Robinson Nov 20, 2016 9:49 AM (in response to Norbert Maijoor)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 Nov 20, 2016 10:23 AM (in response to Zachary Robinson)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 Nov 20, 2016 12:25 PM (in response to Zachary Robinson)2 of 2 people found this helpfulhi 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.

5. Re: Do multiple calculations in a single CASE statement match.
Norbert Maijoor Nov 20, 2016 12:38 PM (in response to Simon Runc)HI Simon,
Thanks for prompt action. Much appreciated

6. Re: Do multiple calculations in a single CASE statement match.
Zachary Robinson Nov 21, 2016 7:32 AM (in response to Simon Runc)Simon,
I'm not sure you understood the problem I'm having, so I made a superstore mockup. 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 Nov 21, 2016 7:54 AM (in response to Zachary Robinson)2 of 2 people found this helpfulYes 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 3month 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

8. Re: Do multiple calculations in a single CASE statement match.
Zachary Robinson Nov 21, 2016 8:16 AM (in response to Simon Runc)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 Nov 21, 2016 8:27 AM (in response to Zachary Robinson)1 of 1 people found this helpfulGlad 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