Specific Date Query with Pivot Table

Diggsy · 08-31-2005, 10:05 AM

I am very new to Pivot Tables. I was given responsibility to maintain a pivot
table on a excel 2000 sheet that shows weekly sales from an Acess
database.The problem is that the report dates that you can see are all
checked off. I was hoping to limit the report to the past 4 weeks only(Maybe
13)but I don't want to uncheck all the dates on the dropdown. I managed to
get into the Microsopft query and in the SQL view I found this query

SELECT WeeklySales.`Report Date`, WeeklySales.`SKU Description`,
WeeklySales.`POG Stores`, WeeklySales.`LW Total Units`, WeeklySales.`LW Total
$`, WeeklySales.`Status Code`
FROM WeeklySales WeeklySales
WHERE (WeeklySales.`Status Code`='Active') OR (WeeklySales.`Status
Code`='Inactive')

Is there a way I can manipulate this to get the data I need or is there
another way of doing this?

Than you

Diggsy

Tom Ogilvy · 08-31-2005, 10:05 AM

If you are in MSQuery, then it sounds like you have a query table rather
than a pivot table - perhaps the pivottable is built off the querytable.

In any event, You should be able to do Add Criteria and limit the records
retrieved by Report Date.

--
Regards,
Tom Ogilvy

"Diggsy" <[email protected]> wrote in message
news:[email protected]...
> I am very new to Pivot Tables. I was given responsibility to maintain a
pivot
> table on a excel 2000 sheet that shows weekly sales from an Acess
> database.The problem is that the report dates that you can see are all
> checked off. I was hoping to limit the report to the past 4 weeks
only(Maybe
> 13)but I don't want to uncheck all the dates on the dropdown. I managed to
> get into the Microsopft query and in the SQL view I found this query
>
> SELECT WeeklySales.`Report Date`, WeeklySales.`SKU Description`,
> WeeklySales.`POG Stores`, WeeklySales.`LW Total Units`, WeeklySales.`LW
Total
> $`, WeeklySales.`Status Code`
> FROM WeeklySales WeeklySales
> WHERE (WeeklySales.`Status Code`='Active') OR (WeeklySales.`Status
> Code`='Inactive')
>
> Is there a way I can manipulate this to get the data I need or is there
> another way of doing this?
>
> Than you
>
> Diggsy

**cowsclaw** · 08-31-2005, 07:32 PM

I'm having a similar issue. I want to pull the last thirteen weeks of data every week, but I dont want to go in and select the next week from the dropdown tree every time. what's the easiest way to have a macro for this?

Thanks.

the thirteen weeks from last week are selected. this is what I get when I deselect the oldest of the thirteen weeks and then add another (this) week; i.e., what i have to do every week.

instead of having it Hide everything I dont want, is there a way to clear everything and then tell it to select only the weeks i want? e.g., i would have a cell with the current week number. then the script would select that week and the twelve weeks before it

ActiveSheet.PivotTables("PivotTable").CubeFields(12).TreeviewControl.Drilled = _
Array(Array("", "", "", ""), Array("[TIME].[All TIME].[2005]", "", "", ""), Array( _
"[TIME].[All TIME].[2005].[Q2]", "[TIME].[All TIME].[2005].[Q3]", "", ""), Array( _
"[TIME].[All TIME].[2005].[Q2].[MM-200506]", _
"[TIME].[All TIME].[2005].[Q3].[MM-200507]", _
"[TIME].[All TIME].[2005].[Q3].[MM-200508]", _
"[TIME].[All TIME].[2005].[Q3].[MM-200509]"))
ActiveSheet.PivotTables("PivotTable").PivotFields("[TIME].[Year Nbr]"). _
HiddenItemsList = Array("[TIME].[All TIME].[2002]", "[TIME].[All TIME].[2007]", _
"[TIME].[All TIME].[2004]", "[TIME].[All TIME].[2001]", _
"[TIME].[All TIME].[2006]", "[TIME].[All TIME].[2003]", _
"[TIME].[All TIME].[2000]", "[TIME].[All TIME].[1999]")
ActiveSheet.PivotTables("PivotTable").PivotFields("[TIME].[Qtr Nbr]"). _
HiddenItemsList = Array("[TIME].[All TIME].[2005].[Q4]", _
"[TIME].[All TIME].[2005].[Q1]")
ActiveSheet.PivotTables("PivotTable").PivotFields("[TIME].[Month Abr]"). _
HiddenItemsList = Array("[TIME].[All TIME].[2005].[Q1].[MM-200501]", _
"[TIME].[All TIME].[2005].[Q1].[MM-200502]", _
"[TIME].[All TIME].[2005].[Q2].[MM-200504]", _
"[TIME].[All TIME].[2005].[Q2].[MM-200505]")
ActiveSheet.PivotTables("PivotTable").PivotFields("[TIME].[Week]"). _
HiddenItemsList = Array("[TIME].[All TIME].[2005].[Q1].[MM-200502].[WW-200506]" _
, "[TIME].[All TIME].[2005].[Q1].[MM-200502].[Week-200507]", _
"[TIME].[All TIME].[2005].[Q1].[MM-200502].[Week-200508]", _
"[TIME].[All TIME].[2005].[Q1].[MM-200503].[Week-200510]", _
"[TIME].[All TIME].[2005].[Q1].[MM-200503].[Week-200511]", _
"[TIME].[All TIME].[2005].[Q1].[MM-200503].[Week-200512]", _
"[TIME].[All TIME].[2005].[Q1].[MM-200503].[Week-200513]", _
"[TIME].[All TIME].[2005].[Q2].[MM-200504].[Week-200515]", _
"[TIME].[All TIME].[2005].[Q2].[MM-200505].[Week-200519]", _
"[TIME].[All TIME].[2005].[Q2].[MM-200505].[Week-200520]", _
"[TIME].[All TIME].[2005].[Q2].[MM-200505].[Week-200521]", _
"[TIME].[All TIME].[2005].[Q2].[MM-200506].[Week-200523]", _
"[TIME].[All TIME].[2005].[Q3].[MM-200509].[Week-200537]", _
"[TIME].[All TIME].[2005].[Q3].[MM-200509].[Week-200538]", _
"[TIME].[All TIME].[2005].[Q3].[MM-200509].[Week-200539]", _
"[TIME].[All TIME].[2005].[Q3].[MM-200509].[Week-200540]")