puppygirl3939
Technical User
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
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