Trailing 30 day sales bar chart
Hi AUG members!
I'm trying to display trailing 30 day sales on a dashboard. I know how to show sales for the past 30 days, but what I'm looking for is the sum of the previous 30 days of sales shown for each day for the past 2 weeks. Perhaps this is a better question for the GI forum, but I can't figure out how to sum the past 30 days of sales for each day with a GI. Any help would be greatly appreciated.
What I've done in the past is to make a column captioned "Days Out" and use that as the Series part of a Column widget. I don't have a "Sales" example, but I have an AP "Due Date" example you should be able to modify to fit your needs.
=Switch(DateDiff( 'd', Today(), [APInvoice.DueDate]) < 0, 'Overdue', DateDiff( 'd', Today(), [APInvoice.DueDate]) >= 30, '30+', 1=1, Right('0'+CStr( DateDiff( 'd', Today(), [APInvoice.DueDate]) ),2))
The idea is you catch anything greater than or less than your range and put it into it's own bucket (Note Overdue and 30+ sections above). Then, the remainder you turn from a number into a String with values such as "00", "01"..."30" for ordering purposes. Filter out your other sections and then create the Widget with "Days Out" as the Series, sort by Legend and your OrigDocAmt as the Value (Sum it).