In order to allow me to focus on Consulting (click here) and Courses (click here), I continue to review all posts here in the Forums, but, in general, I'm only personally responding to posts that I mark as #AcumaticaTnT (click here). For Questions (click here), others may respond, or you can post over at Community.Acumatica.com (click here).
Questions Tricky filter and/or formula
I wrote a GI to display the last sale to customers but need to filter it so that it only shows customers who haven't bought anything in 90 days.
The path I took was looking at last 90 days with 0 total sale and eliminating those with this formula:
=IIf( [SOOrder.OrderDate]>=(DateAdd(Today(), 'd', -90)) and [SOOrder.OrderTotal]=0, [SOOrder.OrderDate], Null)
The issue is it only reports clients with zero sales in their entire history. It does not seem to restrict it to clients with 0 sales in last 3 months.
Can someone suggest a better approach or formula?
GI is attached, please note that I tried conditions but disabled them and went with a filter in the results field.
See the attached xml built using 20.115.0039. Simply grouping the sales orders by customers and aggragting the OrderDate using the Max (OrderDate) and then filtering the resulting GI
I figured I'd come back and post the solution. I had NO idea you could enter a formula in the filters. I tried and it worked.
Thanks for posting that @michaeltriffon. I had a go at this yesterday but couldn't get it to work. I also didn't realise you could do this, nice find!
I had a similar experience here: