So for "Apple" in the Algorta villate, you need to compute 6/13 = 46% and for Artaza, this would be 100% for Apples?
If so, define a calculation field:
Amount / GetSummary ( Amount ; VillageField )
This assumes a single record for Apple in a given village and that your records are sorted by VillageField.
Edit Note: the above expression is incorrect. It should read:
Amount / GetSummary ( sTotal ; VillageField )
Where Amount is the data field and sTotal is a summary field.
Thank you very much for your reply. I am very sorry not to have answered before, as I have been away for a few days.
I understand your answer and it works on the Village level (as you mention, it only works for single concept records in a given village. As you can see in the exemple, it doesn't work in the case of PEARS in PORTU because, we understand now, there are more than one records for PEARS in PORT.
So the next question is: how can we get to the PROVINCE level? The formula does not work there, because there are always more records for each fruit (at least one for each village).
Thank you again for your kind help!!!
Instead of Data field divided by the results of the get summary function, use two get summary functions with different break fields.
GetSummary ( sTotal ; VillageField ) / GetSummary ( sTotal ; ProvinceField )
Should compute the village percentage of the total for the province. To get other fractions, just use different break fields.
Note: I see I made a mistake in my original field using "amount" as though it were both a summary field and a normal data field--which isn't possible of course!
The key detail here is to specify break fields that correctly identify the groups into which your are sorting your records and then to also make sure your records are correctly sorted by these "break" fields.
I have just created the formula following your instructions:
GetSummary ( total cantidad; localidad) / GetSummary ( total cantidad; Provincia)
GetSummary ( s Total; village field) / GetSummary (S Total; Province field)
But to my big surprise the results are ZERO for all the concepts (in the report as well as in the form). I don't understand how this can be possible!
Have you got any idea???
Thank you again!
Is s Total a summary field?
Are your records sroted first by Province Field, Then by Village Field?
Yes, s Total is a summary field.
In my report, the records are sorted first by Province Field, then by Village Field.
And I have established the order by three priorities: Province, Village and Concept.
By the way, the formula you gave us, computes the percentage of the total amount (all concepts together) of each village for the total of the province. But it doesn't compute the percentage of each concept.
Would it be necessary to calculate a Getsummary between concept and village? Like this:
GetSummary ( s Total; concept field) / GetSummary (S Total; Province field) (for the province report)
GetSummary ( s Total; concept field) / GetSummary (S Total; Village field) (for the villages report)
I've got it RIGHT now!!
I did that last thing I suggested to you, using those two formulas, relating concept and province, and then concept and village.
And now my report is correct.
Thanks so much for helping me to reason, it's so hard sometimes to understand the logic behind the tools.
You seem to be understanding the concepts and I don't see any mistakes in how you've set this up. That doesn't explain why you're getting a result of zero, however.
Compare what you've set up to this demo file. (There are two layouts, one with a table view and one set up as a summary report.) http://www.4shared.com/file/UQMoWGBf/PctOfSubTotals.html