Here's the situation:
We have sales forecasts for each state, but then we have actual sales for each state, down to the level of city.
I need to be able to show a crosstab with the columns as state, sales, forecast. But state needs to maintain it's hierarchy in case my manager wants to drill down to see the actual sales were by city. The problem is when I join my forecast table with my sales table, It applies the state's forecast to each city entry (since it was joined on the state). So then my forecasts look bigger than they should be, depending on how many cities we have sales records for.
Example (we forecast total state sales of $8,000)
Resulting raw data:
State City Sales Forecast
AZ Phoenix $3,000 $8,000
AZ Tuscon $4,000 $8,000
AZ Flagstaff $1,500 $8,000
Resulting crosstab:
State Sales Forecast
AZ $8,500 $24,000
How do I create a calculated field that shows my forecast as the total state forecast? It's easy for 1 week's data (use max(Forecast)) but when you get two weeks' data I can't get it to be $8,000 + $8,000
I tried to create a sample file but had troubles
Sure, but I'm still not 100% clear on what you're expecting to see in the workbook.
Does using the ATTR(Forecast) aggregation type, instead of a SUM, solve your problem?
