14 Replies Latest reply on Dec 5, 2013 6:35 AM by Kevin Gafford

    How Can I Filter Out New Clients by Order Date?

    Kevin Gafford

      I have a feeling that this is something simple that I'm overlooking but I've been thinking about it and what I've been trying isn't working.


      What I'm trying to do is create a report that will show all new clients for 2013 based on a database containing all orders.


      What I tried doing was creating a calculated field that holds the sales amount only if sales are pre-2013 so that I could then filter clients with the condition that the sum of pre-2013 sales = 0.  Here's how I entered the fomula:

      If date([orderdate])<date(1/1/2013) then [orderamount] else 0 end


      I figured with this calculated field, I could toss the client field up in the filters box and go to the condition tab > by field > pre-2013 sales / sum / = / 0 and then place clients in the rows and sum(sales) in the columns to take a look at the sum of sales for all new clients, but this formula is returning 0 for every order in the pre-2013 sales column.  It also seems like a clunky method; can anyone suggest a better approach?