0 Replies Latest reply on Nov 6, 2019 2:23 PM by 4010467

    Saved Search - Grouping Transactions by Quarters Over Multiple Fiscal Years (not Calendar Year)

    4010467
    4010467 Nov 6, 2019 2:23 PM

      Hi there,

       

      I’m struggling on one formula.  I have a saved search that’s date range is 4 quarters ago to date… so basically showing me up to 5 quarters.  I want to group it by quarter and I want to do it dynamically, meaning nothing is hardcoded.  I want it to display 2019-Q1, 2019-Q2, etc.  I have done tons of searches, and I see a lot where you can do it based on month, but because I’m spanning across different fiscals, it doesn’t accurately display my quarters (e.g. right now, it will show 2019-Q1, and 2020-Q1, but its displaying incorrect data - it appears to be combining the totals).

       

      This is my latest attempt, but again, I need it to look at year first, and then quarter.  Of course our fiscal is September - August.

       

      CASE WHEN TO_CHAR({trandate}, 'MM') between 9 and 11 then TO_CHAR({trandate}+365,'YYYY') || '-Q1' WHEN TO_CHAR({trandate}, 'MM') = 12 THEN TO_CHAR({trandate}+365,'YYYY') || '-Q2' WHEN TO_CHAR({trandate}, 'MM') between 1 and 2 then TO_CHAR({trandate},'YYYY') || '-Q2' WHEN TO_CHAR({trandate}, 'MM') between 3 and 5 then TO_CHAR({trandate},'YYYY') || '-Q3' WHEN TO_CHAR({trandate}, 'MM') between 6 and 8 then TO_CHAR({trandate},'YYYY') || '-Q4'  END

       

      Hoping someone can let me know if they have any suggestions.