Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drilling down pivot data to classes
Hi experts,
I have an application to track action item status - "open", "close", "postponed", and "overdue". For this I set up the source data to give these info as well the year and calendar week. This I pivoted and charted to get the amount of "open", "close", "postponed", and "overdue" items per week/year. It works well. Now I want to get a picture how long items remain open, similar to: http://www.iceincusa.com/16CSP/content/gifs/mm_3.gif For this I extended my source data and the corresponding PT by the additional columns "days open" (=TODAY()-showUpDate) and "weeks open" (=ROUND(daysOpen/7;0) for each item entry. Example: for an item showed up at 2008-06-17 and being still open, the days open are 232 and the weeks open are 33. A pivot chart is showed by http://tinyurl.com/bh2zg4 But, now I want to show the values of "weeks open" as clusters of 2 (or an individual defineable range) How should I set up my source data / PT? --- Do I need "helper columns" for each class? Thanks for your assistance! Michael |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drilling down pivot data to classes
Michael,
If you use your "Weeks Open" field as a row field (the left-most is better) you can right-click it and choose group, and then fill in the step as 2, 3, 4 whatever... HTH, Bernie MS Excel MVP "Michael.Tarnowski" wrote in message ... Hi experts, I have an application to track action item status - "open", "close", "postponed", and "overdue". For this I set up the source data to give these info as well the year and calendar week. This I pivoted and charted to get the amount of "open", "close", "postponed", and "overdue" items per week/year. It works well. Now I want to get a picture how long items remain open, similar to: http://www.iceincusa.com/16CSP/content/gifs/mm_3.gif For this I extended my source data and the corresponding PT by the additional columns "days open" (=TODAY()-showUpDate) and "weeks open" (=ROUND(daysOpen/7;0) for each item entry. Example: for an item showed up at 2008-06-17 and being still open, the days open are 232 and the weeks open are 33. A pivot chart is showed by http://tinyurl.com/bh2zg4 But, now I want to show the values of "weeks open" as clusters of 2 (or an individual defineable range) How should I set up my source data / PT? --- Do I need "helper columns" for each class? Thanks for your assistance! Michael |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drilling down pivot data to classes
On Feb 4, 10:01 pm, "Bernie Deitrick" <deitbe @ consumer dot org
wrote: Michael, If you use your "Weeks Open" field as a row field (the left-most is better) you can right-click it and choose group, and then fill in the step as 2, 3, 4 whatever... HTH, Bernie MS Excel MVP "Michael.Tarnowski" wrote in message ... Hi experts, I have an application to track action item status - "open", "close", "postponed", and "overdue". For this I set up the source data to give these info as well the year and calendar week. This I pivoted and charted to get the amount of "open", "close", "postponed", and "overdue" items per week/year. It works well. Now I want to get a picture how long items remain open, similar to: http://www.iceincusa.com/16CSP/content/gifs/mm_3.gif For this I extended my source data and the corresponding PT by the additional columns "days open" (=TODAY()-showUpDate) and "weeks open" (=ROUND(daysOpen/7;0) for each item entry. Example: for an item showed up at 2008-06-17 and being still open, the days open are 232 and the weeks open are 33. A pivot chart is showed byhttp://tinyurl.com/bh2zg4 But, now I want to show the values of "weeks open" as clusters of 2 (or an individual defineable range) How should I set up my source data / PT? --- Do I need "helper columns" for each class? Thanks for your assistance! Michael Bernie, GREAT! - it works. Does Grouping works only by row fields? - Sorry for my ignorance, but I'am narrowing down myself to secrets of PTs ;-) Michael |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Drilling down pivot data to classes
Bernie,
GREAT! - it works. Does Grouping works only by row fields? - Sorry for my ignorance, but I'am narrowing down myself to secrets of PTs ;-) It works on columns too - you can just drag the button of interest to the column area, right-click it, choose Group, etc. Bernie |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
create a thematic chart, to show different classes of data | Charts and Charting in Excel | |||
data classes | Excel Discussion (Misc queries) | |||
Preventing new worksheets when drilling down in PIVOTs | Excel Worksheet Functions | |||
are there any blank drilling AFE's on line | Setting up and Configuration of Excel | |||
How do I plot data by drilling down through sheets? | Charts and Charting in Excel |