two same pivot fields

[BorisS]

I'd like to have two occurences of the same pivot data field in one table. I
have a date field, and I'd like a monthly grouped version of it to appear in
the page area, while the detailed date field appears in the rows. In other
words, I want the user to be able to limit the view of dates by the month
that is desired to be viewed. So there are three months of data, and if the
only one that is desired is June, they would select page June (or maybe even
the first level row area, with the second level being the dates themselves).
Calculated field doesn't do it, because that can only go into the data, but I
want something that is a copy of the field in the row area. Any way to avoid
duplicating my source data (it's a good amount)?
--
Boris

[Debra Dalgleish]

When you group the date, group by Months and Days
Move the Month field to the page area, and leave Date in the row area.

BorisS wrote:
> I'd like to have two occurences of the same pivot data field in one table. I
> have a date field, and I'd like a monthly grouped version of it to appear in
> the page area, while the detailed date field appears in the rows. In other
> words, I want the user to be able to limit the view of dates by the month
> that is desired to be viewed. So there are three months of data, and if the
> only one that is desired is June, they would select page June (or maybe even
> the first level row area, with the second level being the dates themselves).
> Calculated field doesn't do it, because that can only go into the data, but I
> want something that is a copy of the field in the row area. Any way to avoid
> duplicating my source data (it's a good amount)?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[BorisS]

Debra, here's the problem. I have a data set that is dynamic and expanding.
I want the pivot to simply reference the columns that are relevant, making it
easy to add data and have it captured in refresh (the users of this will not
be able to constantly update named ranges and such to make sure data is
captured). The grouping doesn't seem to like ANYTHING other than dates in
the field. Even if I say no to blanks, it still doesn't do the trick. Any
way around this?
--
Boris


"Debra Dalgleish" wrote:

> When you group the date, group by Months and Days
> Move the Month field to the page area, and leave Date in the row area.
>
> BorisS wrote:
> > I'd like to have two occurences of the same pivot data field in one table. I
> > have a date field, and I'd like a monthly grouped version of it to appear in
> > the page area, while the detailed date field appears in the rows. In other
> > words, I want the user to be able to limit the view of dates by the month
> > that is desired to be viewed. So there are three months of data, and if the
> > only one that is desired is June, they would select page June (or maybe even
> > the first level row area, with the second level being the dates themselves).
> > Calculated field doesn't do it, because that can only go into the data, but I
> > want something that is a copy of the field in the row area. Any way to avoid
> > duplicating my source data (it's a good amount)?
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>

[Debra Dalgleish]

You could base the pivot table on a dynamic source, as explained here:

http://www.contextures.com/xlPivot01.html

It will automatically expand as new records are added.

BorisS wrote:
> Debra, here's the problem. I have a data set that is dynamic and expanding.
> I want the pivot to simply reference the columns that are relevant, making it
> easy to add data and have it captured in refresh (the users of this will not
> be able to constantly update named ranges and such to make sure data is
> captured). The grouping doesn't seem to like ANYTHING other than dates in
> the field. Even if I say no to blanks, it still doesn't do the trick. Any
> way around this?


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

[BorisS]

Debra, is there any way to get the days to group into bunches of 7 AND have
the months grouped? Seems like I can either do days grouped into a certain
number on their own, OR I can get each day and a month grouping as two
separate fields. But I cannot seem to get months to show up as a field, as
well as having days grouped to anything other than 1. Am I doing something
wrong, or is this the way it has to be? Looking for a cleaner solution than
having to create another column of data (given that I am already at 6K
records), which would just reference the date and take the month of that.
Then I would have the date column, which could be grouped in the table by 7
days (need week periods), and the month column, which could stand on its own.
Let me know if you've come across any other way of handling this.
--
Boris


"Debra Dalgleish" wrote:

> When you group the date, group by Months and Days
> Move the Month field to the page area, and leave Date in the row area.
>
> BorisS wrote:
> > I'd like to have two occurences of the same pivot data field in one table. I
> > have a date field, and I'd like a monthly grouped version of it to appear in
> > the page area, while the detailed date field appears in the rows. In other
> > words, I want the user to be able to limit the view of dates by the month
> > that is desired to be viewed. So there are three months of data, and if the
> > only one that is desired is June, they would select page June (or maybe even
> > the first level row area, with the second level being the dates themselves).
> > Calculated field doesn't do it, because that can only go into the data, but I
> > want something that is a copy of the field in the row area. Any way to avoid
> > duplicating my source data (it's a good amount)?
>
>
> --
> Debra Dalgleish
> Excel FAQ, Tips & Book List
> http://www.contextures.com/tiptech.html
>
>