Help with the formula

Question

Hi All, I have a column named Date. I want to extract quarter from date, but as per our business logic if Date is in the year 2024 and in the month of March and feb it is Q0 and year 2024 and Apr,May,June Q1, and year 2024 July and august is in Q2, year 2024 Sep,Oct and Nov is in Q3 and year 2024 Dec,Jan and Feb 2025 is in Q4.I have figured out a formula which has been saved with no error messages. CASE (
COUNT_DISTINCT(DATE_PART("year", [Date]))= 2024 AND
(
COUNT_DISTINCT(DATE_PART("month", [Date])) = 1 OR
COUNT_DISTINCT(DATE_PART("month", [Date])) = 2 OR
COUNT_DISTINCT(DATE_PART("month", [Date])) = 3
), "Q0",

COUNT_DISTINCT(DATE_PART("year", [Date])) = 2024 AND
(
COUNT_DISTINCT(DATE_PART("month", [Date])) = 4 OR
COUNT_DISTINCT(DATE_PART("month", [Date])) = 5 OR
COUNT_DISTINCT(DATE_PART("month", [Date])) = 6
), "Q1",

COUNT_DISTINCT(DATE_PART("year", [Date])) = 2024 AND
(
COUNT_DISTINCT(DATE_PART("month", [Date])) = 7 OR
COUNT_DISTINCT(DATE_PART("month", [Date])) = 8
), "Q2",

COUNT_DISTINCT(DATE_PART("year", [Date])) = 2024 AND
(
COUNT_DISTINCT(DATE_PART("month", [Date])) = 9 OR
COUNT_DISTINCT(DATE_PART("month", [Date])) = 10 OR
COUNT_DISTINCT(DATE_PART("month", [Date])) = 11
), "Q3",

(
COUNT_DISTINCT(DATE_PART("year", [Date])) = 2024 AND
COUNT_DISTINCT(DATE_PART("month", [Date])) = 12
) OR
(
COUNT_DISTINCT(DATE_PART("year", [Date])) = 2025 AND
(
COUNT_DISTINCT(DATE_PART("month", [Date])) = 1 OR
COUNT_DISTINCT(DATE_PART("month", [Date])) = 2
)
), "Q4",

"Not Specified"
) but when I add this calculated column in the report it is throwing the below error . I am new to Sugar Discover and still in learning stage. Can someone please help. Thank you in advance.

Lahari Pm · Answer

Hi Lahari, and thanks for posting! 
 Two things for you to fix up: 
 1. Remove all the "COUNT_DISTINCT" in the calculation. My guess is that those got there because you dragged fields from the list on the left side of the editor, and that gets added automatically, even when not needed. So in essence you are saying "count the distinct months that are equal to 1" or something like that (it doesn't make sense). This is something that has always confused me, and now that I'm the product manager and you've posted this question, I'm going to work to fix that behavior to be more intuitive. :) 
 
 2. The other thing that is causing an error is the Month numbers. When using DATE_PART above, you'd have to set them equal to MM-Mon (where MM is the two-digit month number, e.g., 02, and Mon is the three-letter month name, e.g., Feb [in English]). Alternatively, you can use the DATE function with Mon and get just the three-letter month name. So this should work as the calculation if you're in English. 
 CASE (
DATE([Date],"yyyy")= "2024" AND
(
DATE([Date], "Mon") = "Jan" OR
DATE([Date], "Mon") = "Feb" OR
DATE([Date], "Mon") = "Mar"
),"Q0",

DATE([Date],"yyyy")= "2024" AND
(
DATE([Date], "Mon") = "Apr" OR
DATE([Date], "Mon") = "May" OR
DATE([Date], "Mon") = "Jun"
),"Q1",

DATE([Date],"yyyy")= "2024" AND
(
DATE([Date], "Mon") = "Jul" OR
DATE([Date], "Mon") = "Aug"
),"Q2",

DATE([Date],"yyyy")= "2024" AND
(
DATE([Date], "Mon") = "Sep" OR
DATE([Date], "Mon") = "Oct" OR
DATE([Date], "Mon") = "Nov"
),"Q3",

(
DATE([Date],"yyyy")= "2024" AND
DATE([Date], "Mon") = "Dec"
) OR
(
DATE([Date],"yyyy")= "2025" AND
(
DATE([Date], "Mon") = "Jan" OR
DATE([Date], "Mon") = "Oct"
)
),"Q4",

"Not Specified") 
 If you're in another language and want to check what those three-letter codes are, simply create a calculation like, 
 Test = DATE([Date],"Mon") 
 and then add the Date field and the Test field to a Pivot report to see what the Test field yields for each date. That will give you what those months are being shown as. 
 
 I hope this helps, and please write back with any other questions!