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:
--- 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?
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
