Calculating certain records on report

[daddypost]

I've got a 52 week sales report, which has a sm's budget for all 52 weeks of the year broken out by period and qtr, giving a total sum for the year at the bottom. Each reps totals roll into a district total, then to a division total. I'm importing weekly sales numbers into the report. The problem I have is my % to budget #'s are way off because I only have 2 weeks worth of sales but 52 weeks worth of budget numbers. Whats the best way to keep the 52 week total showing, keep the 2 weeks sales total showing, but using only the corresponding numbers in budget to show % to sales as of the current week? I can't use anything based on null or 0 as some training reps won't have numbers as of yet. Any help will be appreciated.

 

[Axoliien]

I don't really understand your data and issue. If you could, please put some data and examples of calculations and results to help.

 

[daddypost]

okay, say a rep has a qtrly budget of 250,000 q1,300,000 q2,275,000 q3, and 400,000 q4 giving a total of 1,225,00(each qtr is broken into periods which is broken into weeks)


All of these numbers add to a total shown at the bottom of the report.

Now say we are at the end of the 1st qtr. He has 265000 in sales. He is 106% of budget to date. But since the totals roll up, the budget total is 1,225,000 and his sales total is only 265,000 leaving it looking like 26% of budget at the total line. What I want to do is leave the budget total at 1225000 and the sales total at 265000, but have it calculate % to budget as 265000(sales q1)/250000(budget q1) and have the report look like this

Budget sales % to budget
q1 250000 265000 106
q2 400000 - -
q3 300000 - -
q4 400000 - -

tot 1225000 265000 106(not 26%)
Remember though this is extremely simplified as it is broken down week by week(both budget and sales). The % to budget number total currently is being calculated by dividing total sales by total budget. i want it to cal. sales to date/budget to date. I know its confusing without the actual report with data, so thanks for any help you can give.

 

[PHV]

Providing the sales column is null when not valued you may consider Avg(sales/budget)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244