Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
a query for pivot table | Excel Discussion (Misc queries) | |||
Pivot table query | Excel Discussion (Misc queries) | |||
Pivot Table Query | Excel Discussion (Misc queries) | |||
Pivot Table Query | Excel Discussion (Misc queries) | |||
Pivot Table Query | Excel Programming |