Monday, January 21, 2019

Group the results of a Saved Search based on a date in quarterly basis

To group the results of a saved search based on a date field in quarterly basis, we can use the formula below in the column of a saved search. 

Sample below uses the "Date" ({trandate}) field of a transaction and sets the format of the formula(text) to 'YYYY-Quarter'.

CONCAT(CONCAT(TO_CHAR({trandate},'YYYY'),' - '),CASE WHEN (TO_CHAR({trandate},'MM')='01' or  TO_CHAR({trandate},'MM')='02' or TO_CHAR({trandate},'MM')='03') THEN 'Q1' WHEN (TO_CHAR({trandate},'MM')='04' or  TO_CHAR({trandate},'MM')='05' or TO_CHAR({trandate},'MM')='06') THEN 'Q2' WHEN (TO_CHAR({trandate},'MM')='07' or  TO_CHAR({trandate},'MM')='08' or TO_CHAR({trandate},'MM')='09') THEN 'Q3' WHEN (TO_CHAR({trandate},'MM')='10' or  TO_CHAR({trandate},'MM')='11' or TO_CHAR({trandate},'MM')='12') THEN 'Q4' END)

Example of results

Date Created: 09/03/2013
Results: 2013-Q3

No comments:

Post a Comment