This seems like such a simple idea, I'm surprised I can't find a way to
handle it...

Period Cust Flag Prov
200805 123 Y ON
200805 234 Y ON
200805 345 N ON
200805 456 Y BC
200805 567 N BC

I've built a pivottable on the data above, with Period forming the rows,
Prov and Flag the columns, and Count of Cust in the data section. For
200805, in ON I see 2 customers with flag=Y and 1 with flag=N. In BC, I see
1 customer with flag=Y and 1 with flag=N.

I would like to see each of these values as a percentage of the values
within that province and period. For example, for 200805 in ON I'd like to
see 67% and 33%, in BC, I'd like to see 50% and 50%.

I've tried to use the field settings, and I can show these figures as
percentages of the row or column total, but not as percentages of the number
within the period and province. I can move the province field from a column
to a row, and then display percentage of row total, but the layout isn't
condusive, because there are lots of provinces and periods.

It strikes me as a simple request, to see the count of some value within all
possible values for that field, as a percentage. I've convinced myself that
I'm just missing the right function or field setting.... Is there a
solution?

If you change your layout a bit it is fairly easy.

Move either your Provinces or your flags down into the rows. Now right click
on the customer counts and select field setting - option and change to % of
row. Left as it is you have a bit of an up hill battle.
--
HTH...

Jim Thomlinson


"Mark Parent" wrote:

This seems like such a simple idea, I'm surprised I can't find a way to
handle it...

Period Cust Flag Prov
200805 123 Y ON
200805 234 Y ON
200805 345 N ON
200805 456 Y BC
200805 567 N BC

I've built a pivottable on the data above, with Period forming the rows,
Prov and Flag the columns, and Count of Cust in the data section. For
200805, in ON I see 2 customers with flag=Y and 1 with flag=N. In BC, I see
1 customer with flag=Y and 1 with flag=N.

I would like to see each of these values as a percentage of the values
within that province and period. For example, for 200805 in ON I'd like to
see 67% and 33%, in BC, I'd like to see 50% and 50%.

I've tried to use the field settings, and I can show these figures as
percentages of the row or column total, but not as percentages of the number
within the period and province. I can move the province field from a column
to a row, and then display percentage of row total, but the layout isn't
condusive, because there are lots of provinces and periods.

It strikes me as a simple request, to see the count of some value within all
possible values for that field, as a percentage. I've convinced myself that
I'm just missing the right function or field setting.... Is there a
solution?

Hi Jim:

I have tried moving the provinces to the rows, and using & of row total as
you suggest, but the layout is not condusive to the people reviewing the
data. Because there are a dozen provinces and loads of periods, it's not as
easy to compare as when the results are spread across.

I should correct my wording, where I stated "It strikes me as a simple
request" I should have said something like "It strikes me as a natural
request" !



"Jim Thomlinson" wrote:

If you change your layout a bit it is fairly easy.

Move either your Provinces or your flags down into the rows. Now right click
on the customer counts and select field setting - option and change to % of
row. Left as it is you have a bit of an up hill battle.
--
HTH...

Jim Thomlinson


"Mark Parent" wrote:

This seems like such a simple idea, I'm surprised I can't find a way to
handle it...

Period Cust Flag Prov
200805 123 Y ON
200805 234 Y ON
200805 345 N ON
200805 456 Y BC
200805 567 N BC

I've built a pivottable on the data above, with Period forming the rows,
Prov and Flag the columns, and Count of Cust in the data section. For
200805, in ON I see 2 customers with flag=Y and 1 with flag=N. In BC, I see
1 customer with flag=Y and 1 with flag=N.

I would like to see each of these values as a percentage of the values
within that province and period. For example, for 200805 in ON I'd like to
see 67% and 33%, in BC, I'd like to see 50% and 50%.

I've tried to use the field settings, and I can show these figures as
percentages of the row or column total, but not as percentages of the number
within the period and province. I can move the province field from a column
to a row, and then display percentage of row total, but the layout isn't
condusive, because there are lots of provinces and periods.

It strikes me as a simple request, to see the count of some value within all
possible values for that field, as a percentage. I've convinced myself that
I'm just missing the right function or field setting.... Is there a
solution?

Nine provinces and two territories but who's counting...

Perhaps leave the provinces across the top and move the flags down?
--
HTH...

Jim Thomlinson


"Mark Parent" wrote:

Hi Jim:

I have tried moving the provinces to the rows, and using & of row total as
you suggest, but the layout is not condusive to the people reviewing the
data. Because there are a dozen provinces and loads of periods, it's not as
easy to compare as when the results are spread across.

I should correct my wording, where I stated "It strikes me as a simple
request" I should have said something like "It strikes me as a natural
request" !



"Jim Thomlinson" wrote:

If you change your layout a bit it is fairly easy.

Move either your Provinces or your flags down into the rows. Now right click
on the customer counts and select field setting - option and change to % of
row. Left as it is you have a bit of an up hill battle.
--
HTH...

Jim Thomlinson


"Mark Parent" wrote:

This seems like such a simple idea, I'm surprised I can't find a way to
handle it...

Period Cust Flag Prov
200805 123 Y ON
200805 234 Y ON
200805 345 N ON
200805 456 Y BC
200805 567 N BC

I've built a pivottable on the data above, with Period forming the rows,
Prov and Flag the columns, and Count of Cust in the data section. For
200805, in ON I see 2 customers with flag=Y and 1 with flag=N. In BC, I see
1 customer with flag=Y and 1 with flag=N.

I would like to see each of these values as a percentage of the values
within that province and period. For example, for 200805 in ON I'd like to
see 67% and 33%, in BC, I'd like to see 50% and 50%.

I've tried to use the field settings, and I can show these figures as
percentages of the row or column total, but not as percentages of the number
within the period and province. I can move the province field from a column
to a row, and then display percentage of row total, but the layout isn't
condusive, because there are lots of provinces and periods.

It strikes me as a simple request, to see the count of some value within all
possible values for that field, as a percentage. I've convinced myself that
I'm just missing the right function or field setting.... Is there a
solution?

Ten provinces (since 1949) and three territories (since 1999) but who's
counting!

I've worked around the problem by creating additional fields FlagY and
FlagN, which contain 1 or 0 if the flag is Y or N. I created a calculated
field as FlagY/(FlagY+FlagN), and then included the calculated field in the
pivottable. This seems to be working properly.

I'm still suspicious that there's a simpler, more natural way to express
this, but given the responses, perhaps this can only be achieved with a work
around.

Thanks for your ideas!

"Jim Thomlinson" wrote:

Nine provinces and two territories but who's counting...

Perhaps leave the provinces across the top and move the flags down?
--
HTH...

Jim Thomlinson


"Mark Parent" wrote:

Hi Jim:

I have tried moving the provinces to the rows, and using & of row total as
you suggest, but the layout is not condusive to the people reviewing the
data. Because there are a dozen provinces and loads of periods, it's not as
easy to compare as when the results are spread across.

I should correct my wording, where I stated "It strikes me as a simple
request" I should have said something like "It strikes me as a natural
request" !



"Jim Thomlinson" wrote:

If you change your layout a bit it is fairly easy.

Move either your Provinces or your flags down into the rows. Now right click
on the customer counts and select field setting - option and change to % of
row. Left as it is you have a bit of an up hill battle.
--
HTH...

Jim Thomlinson


"Mark Parent" wrote:

This seems like such a simple idea, I'm surprised I can't find a way to
handle it...

Period Cust Flag Prov
200805 123 Y ON
200805 234 Y ON
200805 345 N ON
200805 456 Y BC
200805 567 N BC

I've built a pivottable on the data above, with Period forming the rows,
Prov and Flag the columns, and Count of Cust in the data section. For
200805, in ON I see 2 customers with flag=Y and 1 with flag=N. In BC, I see
1 customer with flag=Y and 1 with flag=N.

I would like to see each of these values as a percentage of the values
within that province and period. For example, for 200805 in ON I'd like to
see 67% and 33%, in BC, I'd like to see 50% and 50%.

I've tried to use the field settings, and I can show these figures as
percentages of the row or column total, but not as percentages of the number
within the period and province. I can move the province field from a column
to a row, and then display percentage of row total, but the layout isn't
condusive, because there are lots of provinces and periods.

It strikes me as a simple request, to see the count of some value within all
possible values for that field, as a percentage. I've convinced myself that
I'm just missing the right function or field setting.... Is there a
solution?

Add a helper column to your source data:
=1/SUMPRODUCT((Period=Period R)*(Prov=Prov R))
and sum on that. File at:
http://www.savefile.com/files/1615244