Calculations based on a Date Range or Date Parameter

[puppygirl3939]

This is very hard to explain but I am so desperate for help. Hopefully someone can understand. Currently our fiscal year is July 1st to June 30th and our goal is 29,000 in sales. Basically I need to show what "I need" and what "I have" in sales on a weekly basis. This is based on the date of sales. The problem that I am having is that if I have no sales in the first day of each month or no sales on one of the months it does not reflect the correct monthly goal moving forward.

Fields on report: tblsales.sales_dte, tblsales.total_sales and @monthlygoal(formula field)
Parameter field: {?DateSales} This is a date range.
Filter: sales_date = {?DateSales}
@monthlygoal formula: if ({tblsales.sales_dte}) in Date(2004, 07, 01) to Date(2004, 07, 31) then 2416.66
else if ({tblsales.sales_dte}) in Date(2004, 07, 01) to Date(2004, 08, 31) then 4833.32
else if ({tblsales.sales_dte}) in Date(2004, 07, 01) to Date(2004, 09, 30) then 7249.98
etc..... until I reach in Date(2004, 07, 01) to Date(2005, 06, 30) 29000.00

Results based on the following data:
sales_dte total_sales
7.1.04 253
7.22.04 300
8.14.04 1500
8.26.05 850

I enter the parameter {?DateSales} = 7.01.04 to 9.30.04
I should get
Total Sales(Have): 2903
Goal to Date(Need): 7249

Instead I get
Total Sales(Have): 2903
Goal to Date(Need):4833

Because I had no sales in Sept it gives me the goal for August. I also don't like the idea of having to manually enter the dates. I rather use Month in (7 to 7), (7 to 8) etc. But then I had another problem when crossing over to the new year (7 to 1) (7 to 2) etc. Any ideas on how I can correct this problem! Thanks

 

[lbass]

You could use two parms {?startmo} and {?numbermos} (number of months) and then use a selection formula like:

{table.date} in {?startmo} to
dateadd("m",{?numbermos}, {?startmo})

If you are only showing the goal for the entire report, you could use the parameter date in your formula, instead of {table.date}, as in:

select dateadd("m",{?numbermos},{?startmo})
case 7 : 2416.66
case 8 : 4833.32
case 9 : 7249.98
case 6 : 29000.00

-LB

 

[puppygirl3939]

Unfortunately I am force to do this with only one parameter field {?DateSales} I tried using the parameter field but I got an error message indicating that a "number,curreny amount, date, date-time or string is required here" and it places the cursor after the if.

if {?DateSales} in Date(2004, 07, 01) to Date(2004, 07, 31) then 175 else

Do you know if I can write a variable to store the date of a parameter field? What do you think?

 

[lbass]

In my earlier post, the formula should have been:

select month(dateadd("m",{?numbermos},{?startmo}))
case 7 : 2416.66
case 8 : 4833.32
case 9 : 7249.98
case 6 : 29000.00

You absolutely can use parameter fields in formulas. Did you create the parameter first? Also, use the parameter from the field list to make sure you are you using the same name.

However, if you can only use one parameter, then use a range parameter, and create a formula like:

if maximum({?daterange}) in date(2004,07,01) to date(2005,07,31) then 24616.66 else
if maximum({?daterange}) in date(2004,08,01) to date(2005,08,31) then 4833.32 //etc.

-LB

 

[puppygirl3939]

IT WORKS YOU ARE AWESOME. THANK YOU! THANK YOU! THANK YOU!