Howto group Fields in Pivot tables 3

[mbusa]

I have a pivot table where the user can pivot on a field that has dates.

Right now the dropdown shows all the available dates. I need to change that to like a month o year range which the user could select. I think I can group on that field but Cant figure out how to do so.

Any comments/ Suggestions ?

[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[SkipVought]

M.Bajwa,

Your DATE column(s) must be REAL DATES.

EVERY data element in your Date column(s) must contain ONLY REAL DATES -- no empty cells or alpha characters.

In the PT, right click the Date Heading and select Group & Outline/Group -- select whatever combination of grouping you need.

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[dhulbert]

mbusa

You eoither need to manually group the fields by selectign them and going to Tools-Group/Outline and working through that way.
Better would be to add a couple of colums to your source query, one to calcualte the year and one to calcualte the month then use those in the pivot



I love deadlines. I like the whooshing sound they make as they fly by
Douglas Adams
(1952-2001)

 

[mbusa]

thanx for the prompt response.
I tried it and it says
CANNOT GROUP THAT SELECTION

heres the file

http://www.lahoreblog.com/pak/year_date.JPG

http://www.lahoreblog.com/pak/Tpivot.XLS

[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[mbusa]

dhulbert hit the spot ! thats exactly what I am looking for. Too bad I cant figure it out. I can add a new field. ( Calculated field ) but how do I calculate that field ?

[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[SkipVought]

I cannot get to you site.

That message occurrs when there IS or HAS BEEN non-date values in the date column.

1) clean up the data

2) start with a NEW PivotTable.

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[mbusa]

the excel file is 3 megs so it might take soem time to open up. do a "save target as"

[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[AnotherHiggins]

Your source data for the table is in different file - that's why we can't see it.

Try this:
Reformat the date column in the source data to another date format (for example, change from mm/dd/yy to mmm dd yyyy). Did the format in the column actually change? If not, Excel isn't seeing it as a number, but rather as text.

[tt]_____
[blue]-John[/blue]
[/tt][red]"I'm against picketing, but I don't know how to show it."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

FYI:

Why do Dates and Times seem to be so much trouble? faq68-5827

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[mbusa]

Ok the source is here

http://www.lahoreblog.com/pak/source.xls

after reading SkipVought's last post i went back and made the pivot again and it DID group the date to exactly what I wanted so it works but now I have another issue.

even if one of the dates is blank then it does not group on the date field.

What about that case ?

[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[SkipVought]

...even if one of the dates is blank ...

you cannot have ANY empty (blank) date values in the DATE COLUMN for Group to work!

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[mbusa]

Ok.. let me change my statement.
Suppose the source file has 10 records for now But in the range of the pivot tables I have specified Sheet1!$AV:$AX

Instead of the 10 rows I have chosen on the columns so even if more records are added in the source and then the pivot is refreshed it would work.

So if I choose the coulmns it selects (blank) as an option too as the date is blank after the 10 rows. See what I mean ? Whats next !


[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[SkipVought]

Then use a DYNAMIC Range Name for your table that will automatically CHANGE as you add/delete rows of data...

How can I rename a table as it changes size faq68-1331

Use the OFFSET fucntion in Insert/Name/Define.

Put the Range Name in the Pivot Table Source Data Reference INSTEAD of sheet!range:range

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[AnotherHiggins]

Have a look at Dynamic Named Ranges!

See Skip's FAQ on the subject here: faq68-1331. Post back with any questions.

[tt]_____
[blue]-John[/blue]
[/tt][red]"I'm against picketing, but I don't know how to show it."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[mbusa]

I have to admit a couple of things here. Firstly I am a newbie with pivot tables ! Secondly I think the FAQ makes no sense and does not work at all ! ( atleast the worksheet function part )

I have tried a million times to go through the 3 steps and it does not let me define the function with the formula in the FAQ.

I have a table so I skipped step 3 . I will keep trying and let u guys know. appreciate all the help

[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]

 

[AnotherHiggins]

The formula you'll put in the Refers To box is
[COLOR=blue white]=OFFSET(INDIRECT("Sheet1!$A$2"),0,0,COUNTA(Sheet1!$A:$A)-1,1)[/color]
That is the same as what Skip has listed, but if you try to copy and paste his formula it will bring in extra spaces.

You can find more information on the arguments of the OFFSET function in Excel's help file.

[tt]_____
[blue]-John[/blue]
[/tt][red]"I'm against picketing, but I don't know how to show it."[/red]
-Mitch Hedberg

Help us help you. Please read FAQ181-2886 before posting.

 

[SkipVought]

1. Are you getting to the Definenames window?

2. If you are using copy 'n' paste, first copy the function in Item 4
[tt]
=OFFSET
(
INDIRECT("Sheet1!$A$1"),
0,
0,
CountA(Sheet1!$A:$A),
CountA(Sheet1!$1:$1)
)
[/tt]
and be sure to delete the space between OFFSET and (

AND also be sure that the Sheet Name corresponds to YOUR sheet containg the Source Data.

AND that your table's heading are in ROW 1 starting in COLUMN A

AND that the COLUMN A data range contains NO BLANK CELLS.

Skip,

_{[red]Be advised:[/red]To be safe on the FOURTH, don't take a FIFTH on the THIRD, or...
You might not come FORTH on the FIFTH!}

 

[mbusa]

I followed the instructions and I guess taking the spaces out did the trick . Works perfectly now.

Sorry for doubting Skip
maybe you can add a line for newbies like me in the FAQ so we can remove the spaces before adding the code. And while you ar ethere u can fix the typo in step 4.

Thanx again everyone. U guys rule

[highlight]M.Bajwa

http://www.nextgenlogic.com/bajwa

[/highlight]