How to get data in separate columns rather than rows in SmartList Builders (Aging, Sales by Months, etc)

This has been on my list of things to blog about – but when I saw this post in the Forums, I knew its time had come:

I have a client who is attempting to create a Sales report which shows sales by Customer.  They want the report to include a separate column for each month – based on Document Date.  I have attempted to create a calculation for each month (If/Then) but either don’t have the syntax correct or it is truly not going to work.

CASE
WHEN {{Sales Transaction History:Document Date} >1/1/2017…
 
Any ideas?????

This is something that seems to have become a very popular request in the last few years and I have always coded it in SQL. Since it requires a ‘group by’ in SQL code, I can’t imagine being able to do this in SmartList Builder. I would recommend coding this in a SQL view and basing the SmartList on that view.

I would differ 🙂 … This is easily accomplished in SmartList Builder:

First – Add a calculated field for each column of data 

• In this scenario, one for each month – use the Month function to test:
  for January:
  CASE WHEN MONTH({Sales Transaction History:Document Date})=1 then {Sales Transaction History:Originating Document Amount} ELSE 0 END
  and so on…
• For a list with aging in columns (inventory, AR or AP) 
  for example – the calculation for the “31-60 days” bucket for AR by Document Date:
  CASE WHEN
  DATEADD ( d , -30, GETDATE() ) > {RM Open File:Document Date}  AND
  DATEADD ( d , -60, GETDATE() ) <= {RM Open File:Document Date}
  THEN
  (CASE WHEN {RM Open File:RM Document Type-All} > 6
  THEN -{RM Open File:Current Trx Amount}
  ELSE
  {RM Open File:Current Trx Amount}
  END)
  ELSE 0 END
• For other types of data – like quantities on hand – with Sites in columns, here is the calculation for the North site column:
  CASE WHEN {Item Quantity Master:Location Code}=’NORTH’ then {Item Quantity Master:QTY On Hand}
  ELSE 0 END

Next, unselect as Display any fields you don’t want to group by (or sum).  

In the scenario above we would want Year to be a group by field (or we will get all sales in January in history!).  So add a calculated field for year (Users can do a search filter on this field when running the list):

YEAR({Sales Transaction History:Document Date})

Now use the Options icon on the top to make this into a Summary SmartList – the way to “group by” in Builders.   Select all of your column calculated fields as Summary type “Sum” , your other fields as “Group by” and, in the words of my daughter, “There you go!”  

Learn more about summary SmartLists in my post: Summary SmartLists in Builder (Sales by Item for a date range, etc)

Happy Building!
Devon

PS – My apologies and thanks to the ladies on the original forum post!