Specific Date Query with Pivot Table
 

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

Specific Date Query with Pivot Table
 

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" wrote in message
...
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

Specific Date Query with Pivot Table
 

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

Thanks.

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

instead of having it Hide everything I dont want, is there a way t
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 scrip
would select that week and the twelve weeks before it


ActiveSheet.PivotTables("PivotTable").CubeFields(1 2).TreeviewControl.Drille
= _
Array(Array("", "", "", ""), Array("[TIME].[All TIME].[2005]"
"", "", ""), Array( _
"[TIME].[All TIME].[2005].[Q2]", "[TIME].[Al
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].[Yea
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].[Qt
Nbr]"). _
HiddenItemsList = Array("[TIME].[All TIME].[2005].[Q4]", _
"[TIME].[All TIME].[2005].[Q1]")
ActiveSheet.PivotTables("PivotTable").PivotFields( "[TIME].[Mont
Abr]"). _
HiddenItemsList = Array("[TIME].[Al
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].[Al
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]"

--
cowscla
-----------------------------------------------------------------------
cowsclaw's Profile: http://www.excelforum.com/member.php...fo&userid=2685
View this thread: http://www.excelforum.com/showthread.php?threadid=40079

Specific Date Query with Pivot Table
 

can anyone help?


--
cowsclaw
------------------------------------------------------------------------
cowsclaw's Profile: http://www.excelforum.com/member.php...o&userid=26857
View this thread: http://www.excelforum.com/showthread...hreadid=400790

Specific Date Query with Pivot Table
 

please hel

--
cowscla
-----------------------------------------------------------------------
cowsclaw's Profile: http://www.excelforum.com/member.php...fo&userid=2685
View this thread: http://www.excelforum.com/showthread.php?threadid=40079

Specific Date Query with Pivot Table
 

one more try. please help..

--
cowscla
-----------------------------------------------------------------------
cowsclaw's Profile: http://www.excelforum.com/member.php...fo&userid=2685
View this thread: http://www.excelforum.com/showthread.php?threadid=40079