Saved Search Displaying Number of units Sold per Month in Columns

Assume that you want to show total Sales per Month listed per Column for 12 months or a year from today

1. Go to Transactions>Management>Saved Searches>New
2. Select Transaction
3. On the Criteria tab add Main Line is False, Type is Sales Order and Date is Relative from 12 months ago to 0 months ago
4. On the Results tab add Item with Summary Type = Group and the following Formula(Numeric) with Summary Type = Sum

• DECODE(TO_CHAR({today},'MM') - TO_CHAR({trandate},'MM') + 12*(TO_CHAR({today},'YYYY')-TO_CHAR({trandate},'YYYY')),0,{quantity})
• DECODE(TO_CHAR({today},'MM') - TO_CHAR({trandate},'MM') + 12*(TO_CHAR({today},'YYYY')-TO_CHAR({trandate},'YYYY')),1,{quantity})
• DECODE(TO_CHAR({today},'MM') - TO_CHAR({trandate},'MM') + 12*(TO_CHAR({today},'YYYY')-TO_CHAR({trandate},'YYYY')),2,{quantity})
• DECODE(TO_CHAR({today},'MM') - TO_CHAR({trandate},'MM') + 12*(TO_CHAR({today},'YYYY')-TO_CHAR({trandate},'YYYY')),3,{quantity})
• DECODE(TO_CHAR({today},'MM') - TO_CHAR({trandate},'MM') + 12*(TO_CHAR({today},'YYYY')-TO_CHAR({trandate},'YYYY')),4,{quantity})
• DECODE(TO_CHAR({today},'MM') - TO_CHAR({trandate},'MM') + 12*(TO_CHAR({today},'YYYY')-TO_CHAR({trandate},'YYYY')),5,{quantity})
• DECODE(TO_CHAR({today},'MM') - TO_CHAR({trandate},'MM') + 12*(TO_CHAR({today},'YYYY')-TO_CHAR({trandate},'YYYY')),6,{quantity})
• DECODE(TO_CHAR({today},'MM') - TO_CHAR({trandate},'MM') + 12*(TO_CHAR({today},'YYYY')-TO_CHAR({trandate},'YYYY')),7,{quantity})
• DECODE(TO_CHAR({today},'MM') - TO_CHAR({trandate},'MM') + 12*(TO_CHAR({today},'YYYY')-TO_CHAR({trandate},'YYYY')),8,{quantity})
• DECODE(TO_CHAR({today},'MM') - TO_CHAR({trandate},'MM') + 12*(TO_CHAR({today},'YYYY')-TO_CHAR({trandate},'YYYY')),9,{quantity})
• DECODE(TO_CHAR({today},'MM') - TO_CHAR({trandate},'MM') + 12*(TO_CHAR({today},'YYYY')-TO_CHAR({trandate},'YYYY')),10,{quantity})
• DECODE(TO_CHAR({today},'MM') - TO_CHAR({trandate},'MM') + 12*(TO_CHAR({today},'YYYY')-TO_CHAR({trandate},'YYYY')),11,{quantity})
• DECODE(TO_CHAR({today},'MM') - TO_CHAR({trandate},'MM') + 12*(TO_CHAR({today},'YYYY')-TO_CHAR({trandate},'YYYY')),12,{quantity})

6. Hit Save and Run

The DECODE() formula acts as an if/then function where the first parameter is evaluated. If it matches the second parameter then it will display the third parameter. In this case, the expression:

TO_CHAR({today},'MM') - TO_CHAR({trandate},'MM') + 12*(TO_CHAR({today},'YYYY')-TO_CHAR({trandate},'YYYY'))

is being evaluated. The algorithm is (1) convert dates to months and (2) get the difference between the transaction month and current month. The difference is in terms of months. Therefore, 0 means current month, 1 means one month ago, 2 means 2 months ago, etc.

The first part of the expression takes care of the difference. The second part of the expression takes care of periods that cross over a year. For example, if today is February 2013 then using TO_CHAR({today},'MM') converts this to 2 since February is the 2^nd month of the year. A transaction in December 2012 using TO_CHAR({trandate},'MM') will be converted to 12 since December is the 12^th month of the year.

Therefore, the difference is (2-12) = -10 which is not correct. December to February has a 2-month difference. This is why the second expression is added. 12*(TO_CHAR({today},'YYYY')-TO_CHAR({trandate},'YYYY')) means 12 * (2013 – 2012) = 12 * 1 = 12. This is then added to the first expression. The final result therefore is (2 – 12) + 12 (1) = -10 + 12 = 2, which is the expected result.

The advantage of this Saved Search is that is uses the {today} variable which makes this up to date every time a user runs it. This concept can be used on any other period-reporting problems like an Inventory Aging Report. In this case instead of Sales Orders, Inventory transactions will be listed (e.g. Item Receipt, Item Fulfillment, Inventory Adjustment, etc.).