I am trying to sum up a number in one field for all records that have the same value as another, current field. So if A is a numeric field, and B is a text field, this would be the sum of all the values in the A field that have the same value as whatever is in B currently. I would think that this would be aggregate(«A»,“sum”,{«B»=cache(«B»,“temp”)}). However, even though cache(«B»,“temp”) gives the proper value of B, say C, and if I just put the constant C in its place I get the proper sum, {«B»=cache(«B»,“temp”)} in the place of C gives the sum of all of field A. How can I get just the sum for all records that have the value of the current record in B?
The cache( function is being reevaluated for each record, and it simply returns the value of its first.parameter, which is the value of the B field. So you are simply comparing B to B to see if they are equal.
If you just use a local variable, instead of the cache( function it should work, provided this is an integer field, and not float.
I would like to use this as a formula in a text object. That is why I am looking for a way to make this work for each individual record. I suppose I could use another field that is filled with that sum for every record with the same value of B.
However, your response gives me a better idea of how aggregate( works.
It’s a bit indirect and practicality depends on the number of records you are dealing with - but instead of summing, you could select the matching records and then multiply the count of selected records by your matching number.
You don’t have to reduce the number of visible records (because of the selection) to do that. You could just build an array with the selection criteria, and then use the number of elements in the array.
For example - and I’ll skip the part of changing the “text” number into a “number” number - you could create an array of records matching your number, then multiply that match number by the number of elements in the array.
Those steps are probably easier in a procedure than a formula. But it seems, in PanX, you often have a choice between formula and procedure - like in a text object?
If you choose this path, make sure you understand an array’s characteristics - especially around zero elements and if you are also counting the record that has your match number.
Dave has correctly diagnosed the problem, and his suggestion of using a variable should work. However, that’s tricky to do in a text object (though I think possible with the assignglobal( and ignore( functions).
Fortunately the aggregate( function has a built in way to do what you want:
If you’ve been following the forum closely you may be aware that this feature wasn’t working in a couple of recent releases. However, it does work in the very latest release.
I had trouble getting the very latest release. Update worked fine on one computer, and hung on the one with the file that I was working on. Downloading the demo gave me an older version. Eventually I was able to copy from one computer to the other, so I have build 4555 on both. It would be useful to have a date when you post a new version.
However, I am still not able to make what I want to work. I am still getting the sum of A for all records, not just those which B is equal to the value of B for the current record. I have tried a number of things, including assigning the current value to a variable.
Sorry, it appears that the double chevron trick only works if you are calculating an average from another database, not the current database.
I was able to get it to work with a variable. I used a checkbook database with fields Description and Total. This formula in a text display object will show the total of the checks with the same description as the current record. As you move from record to record, the total will update.
ignore("",assignglobal(Description+"","globalDescription"))+pattern(aggregate({Debit},"sum",{Description=globalDescription}),"#,.##")
Unfortunately there is no way to do this with a fileglobal variable. So be sure to pick an unusual name for the variable. If this were a database I was actually using, I would probably use a longer name than I did in this example.
Well, you can just click on the ProUE News & Blog category here on the forum.
That’s probably because this afternoon I was in the process of uploading the 4555 version, and that process wasn’t complete yet. The demo is the last place where the update appears.