Expose the Number of Transactions Created per Hour in Saved Search

On a Transaction Saved Search, the user wants to expose the number of Transactions Created per Hour.

Solution

1. Navigate to Lists > Searches > Saved Searches
2. Find the Saved Search in concern
3. Click  Results 
4. Click Columns
5. Fields:
   • Select Formula (Numeric)
     • Summary Type: Select Maximum
     • Function: Select Round
     • Formula: Enter sum(case when {‌systemnotes.type} = ‘Create’ and {‌systemnotes.field} = ‘Record’ and to_char({‌systemnotes.date},’hh24′) = ’16’ then 1 else 0 end)/NULLIF(count(distinct case when {‌systemnotes.type} = ‘Create’ and {‌systemnotes.field} = ‘Record’ then to_char({‌systemnotes.date},’YYMMDD’)  end),0)
     • Summary Label: Total No. of Transactions (4pm – 5pm)
       
       Note: The expected result will be the number of transactions created from 4:00-4:59 pm. Should the user want to modify the time range, just edit the to_char({‌systemnotes.date},’hh24′) = ’16’ expression.
       
6. Click Save & Run