Scenario
User wants to create a Saved Search that will show Customer Balances for the past 12 months.
Solution
Follow the steps below to show the trend of Customer Balances for the past 12 Months:
1. Navigate to Reports > Saved Searches > All Saved Searches > New> Transaction
2. On the Criteria tab > Standard sub tab, set the following filters:
--Account Type > is Accounts Receivable--Posting > is true
3. On the Results tab > Columns sub tab, add the following fields:
--Field = Name | Summary Type = Group--Field = Amount | Summary Type = Sum | Custom Label = Current Balance--Field = Formula (Currency) | Summary Type = Sum | Formula = case when {trandate} <= add_months({today},-1) then {amount} else 0 end | Custom Label = 1 Month Ago--Field = Formula (Currency) | Summary Type = Sum | Formula = case when {trandate} <= add_months({today},-2) then {amount} else 0 end | Custom Label = 2 Months Ago--Field = Formula (Currency) | Summary Type = Sum | Formula = case when {trandate} <= add_months({today},-3) then {amount} else 0 end | Custom Label = 3 Months Ago--Field = Formula (Currency) | Summary Type = Sum | Formula = case when {trandate} <= add_months({today},-4) then {amount} else 0 end | Custom Label = 4 Months Ago--Field = Formula (Currency) | Summary Type = Sum | Formula = case when {trandate} <= add_months({today},-5) then {amount} else 0 end | Custom Label = 5 Months Ago--Field = Formula (Currency) | Summary Type = Sum | Formula = case when {trandate} <= add_months({today},-6) then {amount} else 0 end | Custom Label = 6 Months Ago--Field = Formula (Currency) | Summary Type = Sum | Formula = case when {trandate} <= add_months({today},-7) then {amount} else 0 end | Custom Label = 7 Months Ago--Field = Formula (Currency) | Summary Type = Sum | Formula = case when {trandate} <= add_months({today},-8) then {amount} else 0 end | Custom Label = 8 Months Ago--Field = Formula (Currency) | Summary Type = Sum | Formula = case when {trandate} <= add_months({today},-9) then {amount} else 0 end | Custom Label = 9 Months Ago--Field = Formula (Currency) | Summary Type = Sum | Formula = case when {trandate} <= add_months({today},-10) then {amount} else 0 end | Custom Label = 10 Months Ago--Field = Formula (Currency) | Summary Type = Sum | Formula = case when {trandate} <= add_months({today},-11) then {amount} else 0 end | Custom Label = 11 Months Ago--Field = Formula (Currency) | Summary Type = Sum | Formula = case when {trandate} <= add_months({today},-12) then {amount} else 0 end | Custom Label = 12 Months Ago
4. Rename the search
5. Click on Save & Run