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
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations SkipVought on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Pivot Tables in Excel 2
- Thread starter jbanas
- Start date
-
1
- #2
SkipVought
Programmer
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]
![[glasses]](/data/assets/smilies/glasses.gif "[glasses] [glasses]")
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!
![[tongue]](/data/assets/smilies/tongue.gif "[tongue] [tongue]")
[/sub]-
1
- #3
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...............
----------------------------------------------------------------------------
![[peace]](/data/assets/smilies/peace.gif "[peace] [peace]")
It's easier to beg forgiveness than ask permission![[2thumbsup]](/data/assets/smilies/2thumbsup.gif "[2thumbsup] [2thumbsup]")
----------------------------------------------------------------------------
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----------------------------------------------------------------------------
- Thread starter
- #4
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
Programmer
"...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]- Thread starter
- #6
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.
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
Programmer
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]- Thread starter
- #8
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.
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
Programmer
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]- Thread starter
- #10
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
----------------------------------------------------------------------------
Regards
Ken..........
----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission----------------------------------------------------------------------------
SkipVought
Programmer
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]- Thread starter
- #13
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!!
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!!
- Status
