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.
select sheet.twbx 1.2 MB
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.
2 of 2 people found this helpful
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.
Thanks for prompt action. Much appreciated
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.
CALC ONE (month = 3)
CALC TWO (month = 6)
CALC THREE (month = 9)
CALC FOUR (month = 12)
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!
2 of 2 people found this helpful
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
1 of 1 people found this helpful
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