Pivot Tables in Excel 2

[jbanas]

I am trying to reproduce a better way in building a pivot table. I am dealing with years of data but I want the end user to select a date say November 1st and then show the past 90 days in a pivot table. Is it possible with checking all the dates in the drop down. Any help would be great

 

[SkipVought]

Hi,

PivotTables are great tools for getting a quick summary of large amounts of data.

However, sometimes its a paint to be able to display only a portion of the data. The Filters are a bit unwieldy.

I might build a control using a new column with a formula that takes the selected date (maybe from a cell OUTSIDE the PT on the sheet that the PT is displayed) and flags rows between that date and that date minus 90 days. Drag the new column to a PAGE field and only display the FLAGED rows.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[KenWright]

Exactly how I'd do it, but don't forget to refresh the PT anytime you change that date.

If you want an example of a formula, then something like the following will do what you want:-

Assuming your dates in say Column F and a cell containing your date variable on a tab called "Report" in say cell L1, put this in another column and copy down. Then just do as Skip suggested and filter in the Page fields on "SHOW"

=IF(AND(F2>(Report!$L$1-90),F2<=Report!$L$1),"Show","Hide")

Regards
Ken...............

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[jbanas]

thanks guys the post worked but im still having an issue....here is where I am confused on if it will work or not...my dates are going back as far as 1999. I have a separate column (=IF(Q2<=93,$T$2-Q2, ") that i am determining whether or not the date shows up as being less than 92 days. I then drag that column into the row section of the pivot, and the year up at the column and then I am counting the account. My issue is the dates that are getting classified as blank then show up as part of the count. I want to show the last 3 years and the count of the accounts unfortunately when i take out the blank date it only will show the one year. Can this be done

 

[SkipVought]

"...whether or not the date shows up as being less than 92 days"

Huh?

Maybe you ought to post some data that illustrates your problem.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[jbanas]

Count of Account Year
Custom_Date 2007 2006 2005 2004 2003
1 1457 1927 2474 3190 3581
7/9/2007 1461 1927 2474 3190 3581
7/10/2007 1467 1927 2474 3190 3581
7/11/2007 1472 1927 2474 3190 3581
7/12/2007 1478 1927 2474 3190 3581
7/13/2007 1485 1927 2474 3190 3581
7/16/2007 1489 1927 2474 3190 3581


enclosed is a view of the pivot table data. The date field contains the date - the 92 days and all the other dates in the table (represented as the 1). When I remove the 1 from my Dates entered it will only show the one year 2007.

 

[SkipVought]

Your SOURCE DATA is mor important to see than your PT. That's where the problem is. Please explain EXACTLY what you are doing.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[jbanas]

Acct C_D Julian Date_Ed Cur_Date
a 6/10/1999 93 8-Oct-07
b 3/13/2007 93
c 5/2/2007 93
d 4/4/2007 93
e 7/18/2007 82 18-Jul-07
f 9/11/2007 27 11-Sep-07
g 7/30/2007 70 30-Jul-07
f 9/4/2007 34 4-Sep-07
i 1/31/2007 93

this is just a quick snippet of the source data. I have the field which gets todays date (cur_date). I then take the (cur_date) and see if the (c_d) is within my 92 day range. I figure out how many days from the(Cur_Date) by calculating the (Julian) field. The (date_ed) then displays the date. The date entered is then brought over to the pivot table which will then perform a count on the (acct) on that day.

 

[SkipVought]

You have stuff in your head that you are not communicating.

"The date field contains the date - the 92 days and all the other dates in the table (represented as the 1)."

Where does the 1 come from? Is there a FORMULA?

What Source data fields get draged to what ROW, COLUMN, PAGE & DATA fields.

Please provide a coherent example of SOURCE & PT Results that illustrate the issue you are asking about.

Please be clear, concise and complete.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[jbanas]

i got it to work...thanks very much for everyones help. Here is a question for you...in a pivot table..if a field contains no data that is selected...is there a way to hide it from showing up in the drilldown?

 

[KenWright]

If it's a specific field that you are looking at (Assume column B) then you could always just add another column into the source data, and use a formula such as =IF(B2="","HIDE","SHOW"), and then just drag that field into the Page fields and filter on "SHOW"

Regards
Ken..........

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission
----------------------------------------------------------------------------

 

[SkipVought]

Also, please post your solution, as there are many other Tek-Tip members that browse and search, in order to learn and get ideas.

Skip,
[sub]
When a group touring the Crest Toothpaste factory got caught in a large cooler, headlines read...
Tooth Company Freeze a Crowd! and
Many are Cold, but Few are Frozen![/sub]

 

[jbanas]

Solution:

I had a formula =IF((O2-L2)> 93, 93, IF((O2-L2)<-32, -32, (O2-L2))) that determined whether or not something was greater than 93 meaning the days were over 93 or less than -32, otherwise take (02-l2). In the next formula i assumed that everything would happen on the first of the month so i took the month from cell (02) and concatenated an "01" to it assuming everything would occur on the first of the month and the current year =VALUE((MONTH(O2)&"/01/"&YEAR(NOW()))). I then took =VALUE((MONTH(O2)&"/01/"&YEAR(NOW()))) and subtracted from the above formula to give me the month and day which i used in my pivot chart. I formated the column to date. if the user selects a month in the pivot chart (i.e august) it will then figure out 93 days back from 8/1/07 and put the values on the chart.

thanks everyone for all your help!!