Pivot Table questions

[aells]

OK this is a good one.

I have two pivot tables, each has the same data, except one is Forecast and one is actual.

Columns are month
Rows are Customer and then part number

data is Units and Total Sales.in columnar format.

What I would like to be able to do is create a new pivot table that will take the actuals to date and use them instead of the forecast totals.

Is there a way to do this easily?

 

[Fazza]

Hi,

It should be easy enough. Just create one pivot table directly from the two source data and in the SQL select the forecasts and actuals. Such as, at the first step of the pivot table wizard take the external data option. The SQL might be something like below - use whatever syntax works for you for the date filtering. It might depend on your database?

HTH, Fazza

SELECT *
FROM actuals
WHERE actuals.some_date < Date
UNION ALL
SELECT *
FROM forecasts
WHERE forecasts.some_date >= Date