YAPTQ - Yet Another Pivot Table Question

[bdaoust]

Guess I don't understand pivots like I shoud! (Yes, books are on the
way)

Anyway, data looks like this:

SubmittedDate 0-15 16-30 31-45 >45 PxAmount
01/01/2006 1 0 0 0 $10.00
01/05/2006 0 1 0 0 $20.00
01/06/2006 0 1 0 0 $40.00
02/05/2006 0 0 1 0 $50.00

Is there a way to get the pivot tableto look like this

0-15 days 16-30 days 31-45 days etc
Jan 1 2
Feb 0 0 1

PxTot $10 $60 $50

I can get this to work with Sum of PX in the ROW area, but they want
the Sum of PX to be in the Col area.

TIA
Brian

 

[Debra Dalgleish]

With the way your data is set up, there's no connection between the
PxAmount and the Day groups, so I don't see a way that you can get the
total amount per group.

If you had the group calculated in a column, you could create two pivot
tables -- one to show count per group per month, and the other to show
total PxAmount per group.

The source table would be:

Date Count Group PxAmount
01/01/2006 1 0-15 $10.00
01/05/2006 1 16-30 $20.00
01/06/2006 1 16-30 $40.00
02/05/2006 1 31-45 $50.00

In one pivot table, group the date by month, in the row area
Put Count in the data area, and Group in the Column area

In the other pivot table, put PxAmount in the data area and Group in the
column area.

 

[bdaoust]

Debra:

What if I place the pxamount in SumOf 0-15 Sum Of 16-30 etc. cols
using an IF statement like this:

=IF(AND(Q2>=0,Q2<=15),G2,0)

Q2 being the days it took and G2 being the pxAmount

Thanks
Brian

 

[Debra Dalgleish]

That would let you show the PxAmount per day grouping, but then the
count per month wouldn't be available.

Do you have many rows of data? Maybe it would be easier to summarize
using formulas, instead of a pivot table.

Debra:

What if I place the pxamount in SumOf 0-15 Sum Of 16-30 etc. cols
using an IF statement like this:

=IF(AND(Q2>=0,Q2<=15),G2,0)

Q2 being the days it took and G2 being the pxAmount

Thanks
Brian

 

[bdaoust]

Debra:

It's about 18,000+ rows of data. The reason I try to do pivots is so
that they can look at "buckets" of data and then drill to the details
for that bucket.

I ended up going with the suggestion of your first reply. Works well
and they actually like the two pivots, so good news.

Now, that I have the 0-15, 16-31 buckets, they each have a count in
there. (some dont have any data because nothing falls in that bucket.)


So now for data I have Sum Of Group, for rows I have the submitted date
(Jan, Feb, etc), and for column I have group. Looks good. And there
is a grand total at the end for each month. Is there a way to show the
percentage of each group compared to the grand total.

I really wish one could post screen shots here. It would help to show
what I have and the desired result.

Thanks
Brian

That would let you show the PxAmount per day grouping, but then the
count per month wouldn't be available.

Do you have many rows of data? Maybe it would be easier to summarize
using formulas, instead of a pivot table.

 

[Debra Dalgleish]

Thanks for letting me know that the two pivot suggestion worked. You
could add another copy of the data field to the pivot table, and set it
to show the % of Row.

To keep it clean though, you might prefer to make one more pivot table,
and just show the percentages. Or, calculate the percentages using
formulas outside the pivot table.

Debra:

It's about 18,000+ rows of data. The reason I try to do pivots is so
that they can look at "buckets" of data and then drill to the details
for that bucket.

I ended up going with the suggestion of your first reply. Works well
and they actually like the two pivots, so good news.

Now, that I have the 0-15, 16-31 buckets, they each have a count in
there. (some dont have any data because nothing falls in that bucket.)


So now for data I have Sum Of Group, for rows I have the submitted date
(Jan, Feb, etc), and for column I have group. Looks good. And there
is a grand total at the end for each month. Is there a way to show the
percentage of each group compared to the grand total.

I really wish one could post screen shots here. It would help to show
what I have and the desired result.

Thanks
Brian