Create a Saved Search to Display Quantity Sold per Month in Columns Similar to Sales by Item Report

Saved Searches provide more flexibility over Saved Reports in terms of adding other fields, using formula, etc. On this note, there are instances when users would want to track Sales per Month using a Saved Search similar to the Sales Order or Sales by Item Report where figures are displayed on a per column basis. By design, transactions are displayed on a per row basis and grouping these will show aggregate sales on each row and not on each column. As a solution:

 

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.).