Percent of subtotal in a pivot table

[#1]

Hi,

I have a pivot table like this:

Mounth       Location      # People

Gen            Florence     1000

Gen            Milan          4000

Feb             Florence      500

Feb             Milan          1500

i'm tring to add a column in my pivot table that calculte the percentage of the misure #People over the subtotal of  #People per month.

Like this

Mounth       Location      #People  %

Gen            Florence     1000        20%

Gen            Milan          4000        80%

Feb             Florence      500         25%

Feb             Milan          1500         75%

I tried the following formula :

Count(distinct idPeople)/Count(Total <Mounth> idPeople)

This it's fine until i add the set analysis formula{$<DataStart={"<=$(=Date(MaxData)) >=$(=Date(MinData))"}>} to customize the time period.

I tried to put  {$<DataStart={"<=$(=Date(MaxData)) >=$(=Date(MinData))",Mounth= }>} instead to use TOTAL but dosn't work.

( The column have the sign "- ").

How can i do it?? Where is the mistake?

Thanks a lot

Ale

[swuehl · Accepted solution]

I assume using the set expression in your count does work (sometimes correctly matching the date format is kind of hard in a set expression field modifier search expression)?

=count({$<DataStart={"<=$(=Date(MaxData)) >=$(=Date(MinData))"}>} distinct idPeople)

Then it should also be ok to combine the set with the Total qualifier in your denominator:

count({$<DataStart={"<=$(=Date(MaxData)) >=$(=Date(MinData))"}>} distinct total<Mounth> idPeople)

What do you see as result?

[#3]

It's fine!!!

I tried this solution but maybe i made something wrong in the formula because before i saw only the ' - ' symbol in the column.

Thank you  so much

[gershova]

Check, if the FieldName after TOTAL keyword  mention  set in the < >. It is important (and helps in my case)

[#5]

Can you post your formula?