Saved Search displaying Average Monthly Expenses

This saved search will display running average monthly expenses as of a certain month, with each month displayed under a separate column to see on any increase or decrease versus budgeted expenses.

 

Expenses will refer to all expenses posted in via transaction GL impact.  You may include additional filters to narrow down to list of expenses to be included.

To create the saved search:

 

1. Navigate to Lists > Search > Saved Searches > New. Select Transaction as search type.

2. At the Criteria tab > Standard subtab, enter the following filters:

-----a. Account type - at the pop up select Expense

-----b. Posting - at the pop up select Yes

-----c. Date - at the pop up select within this fiscal year

 

3. At the Results tab > Column subtab, enter the following:

-----a. Account-----Summary Type: Group

-----b. Formula (currency)-----Summary Type: Sum ----- Formula (enter the following at the pop-up): sum(case when to_char({trandate}, 'MM') = '01' then -nvl({amount},0) else 0 end)/1 ----Summary Label: JAN

-----c. Formula (currency)-----Summary Type: Sum ----- Formula (enter the following at the pop-up): sum(case when to_char({trandate}, 'MM') = '02' then -nvl({amount},0) else 0 end)/2 ----Summary Label: FEB

-----d. Formula (currency)-----Summary Type: Sum ----- Formula (enter the following at the pop-up): sum(case when to_char({trandate}, 'MM') = '03' then -nvl({amount},0) else 0 end)/3 ----Summary Label: MAR

-----e. Formula (currency)-----Summary Type: Sum ----- Formula (enter the following at the pop-up): sum(case when to_char({trandate}, 'MM') = '04' then -nvl({amount},0) else 0 end)/4 ----Summary Label: APR

-----f. Formula (currency)-----Summary Type: Sum ----- Formula (enter the following at the pop-up): sum(case when to_char({trandate}, 'MM') = '05' then -nvl({amount},0) else 0 end)/5 ----Summary Label: MAY

-----g. Formula (currency)-----Summary Type: Sum ----- Formula (enter the following at the pop-up): sum(case when to_char({trandate}, 'MM') = '06' then -nvl({amount},0) else 0 end)/6 ----Summary Label: JUN

 

**For the rest of the months, just copy the formula and change the month as appropriate.