Previous Year Sales Formula

[tsanborn]

I have a report that was already wrote that shows that last 12 months sales from today's date. But I want this same report to show what the sales were for a date range. Example would be: 1/1/2014 - 12/31/2014
I want replace the current formula with that date range any suggestions: I have included the formula below.

OrganizationLocations INNER JOIN (SELECT arpCustomerOrganizationID,arpShipLocationID, SUM(arpInvoiceTotalBase) AS InvoiceTotalBase, SUM(arpInvoiceBalanceBase) AS arpInvoiceBalanceBase FROM ARInvoices WHERE arpOverpayment = 0 And arpInvoiceDate >= DATEADD(yyyy,-1,GETDATE()) GROUP BY arpCustomerOrganizationID,arpShipLocationID) As Test ON cmlOrganizationID=arpCustomerOrganizationID And cmlLocationID = arpShipLocationID Left Outer Join ORGANIZATIONLOCSALESPEOPLE On CMLORGANIZATIONID = CMKORGANIZATIONID And CMLLOCATIONID = CMKLOCATIONID

Thank you for your time

 

[LaurieHamlin]

Set up parameters for start date and end date.

 

[tsanborn]

Thank you for you quick reply but that is my problem I am not sure how to complete that portion.

 

[LaurieHamlin]

I am using XI. Without knowing your version I can only tell you how I do it in XI. Open the Field Explorer, click on Parameter Fields and click on Create New. It basically walks you through the rest. I hope that helps.

 

[Charliy]

Create the parameter with type = Date and allow Range value = true

Then make your select formula include: {table.date} in {?Date Range}

 

[BettyJ]

arpInvoiceDate between to_date ('01-01-2014', 'mm-dd-yyyy') and to_date ('12-31-2014', 'mm-dd-yyyy')

or

(arpInvoiceDate >= to_date ('01-01-2014', 'mm-dd-yyyy') and arpInvoiceDate<=to_date ('12-31-2014', 'mm-dd-yyyy') )