Home > Calculated Fields, General - Builders, Inventory, Receivable Management, Sales > How to get data in separate columns rather than rows in SmartList Builders (Aging, Sales by Months, etc)

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!

About these ads

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 25 other followers