Customize saved search to show Amount per Quarter

Solution:

Currently in saved searches the Date fields can be set to show as Months, Quarter, or Year in the function column of the Results tab. However, they will show in rows such as below:

 

Q1 Amount

Q2 Amount

Q3 Amount

Q4 Amount

 

In order to display the Amount per quarter in column, a formula can be used as follows:

 

Sample Scenario: Saved search for Invoices for last year showing the Quarterly Amount in Columns per Customer.

 

1. Reports > Saved Searches > All Saved Searches > New

 

2. Select "Transaction"

 

3. Set the following filters in the Criteria tab:

            Type = is any of Invoice

            Date = within Last Fiscal Year

            Main Line = is true

 

4. Set the following fields in the Results tab:

            Name | Summary Type = Group

 

5. Enter four fields for Formula (Currency) and set the Summary Type to Sum

 

6. Under the Formula column, set the following formula and Custom Summary Labels:

            1. Summary Label = Q1

Formula = case when to_char({trandate}, 'MM') between 1 and 3 then {totalamount} else 0 end

 

            2. Summary Label = Q2

Formula = case when to_char({trandate}, 'MM') between 4 and 6 then {totalamount} else 0 end

 

            3. Summary Label = Q3

Formula = case when to_char({trandate}, 'MM') between 7 and 9 then {totalamount} else 0 end

 

            4. Summary Label = Q4

Formula = case when to_char({trandate}, 'MM') between 10 and 12 then {totalamount} else 0 end

 

7. Save and Run

 

Notes:

 

** The above formula means that 'if the transaction date ({trandate}) is within the 1st and 3rd months, 4th and 6th months, 7th and 9th months, and 10th and 12th months, then show their total transaction amount ({totalamount})'.

 

** The internal ID of the Date field is {trandate} and Amount (Transaction Total) is {totalamount}. These fields can be altered depending on the date and amount fields needed.