Financial years can be different for every company. Certain companies financial year starts from 1 July to 30 June the following year or others will be 1 March to 30 February the following year, but how do we display this in a saved search? The default quarter functionality only shows the default year from Jan – Dec.
In the following search, I need to group Opportunities by Quarter for the company financial year that is from Feb – Jan.
- Add a Formula(Text) field.
- Add Formula
CASE WHEN To_Char({closedate},’MM’) = ’01’ THEN ‘Q4′ WHEN To_Char({closedate},’MM’) = ’02’ THEN ‘Q1′ WHEN To_Char({closedate},’MM’) = ’03’ THEN ‘Q1′ WHEN To_Char({closedate},’MM’) = ’04’ THEN ‘Q1′ WHEN To_Char({closedate},’MM’) = ’05’ THEN ‘Q2′ WHEN To_Char({closedate},’MM’) = ’06’ THEN ‘Q2′ WHEN To_Char({closedate},’MM’) = ’07’ THEN ‘Q2′ WHEN To_Char({closedate},’MM’) = ’08’ THEN ‘Q3′ WHEN To_Char({closedate},’MM’) = ’09’ THEN ‘Q3’WHEN To_Char({closedate},’MM’) = ’10’ THEN ‘Q3′ WHEN To_Char({closedate},’MM’) = ’11’ THEN ‘Q4’WHEN To_Char({closedate},’MM’) = ’12’ THEN ‘Q4’ else ‘0’ end
- In the above formula, we extract the month from the date and check the month and assign the quarter. You can change the formula to suit your calendar year, by changing the “Q1” that standards for Quarter 1 after THEN
0 Comments