Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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?
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?
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
Check, if the FieldName after TOTAL keyword mention set in the < >. It is important (and helps in my case)
Can you post your formula?