Using the aggregate( function inside the arrayfilter( function


#1

Is it possible to use the aggregate( function as the formula in the arrayfilter( function? This code:

local lvtally,lvtemp
lvtally=""
lvtemp=arraybuild(cr(),"",{«StateProvince»})
lvtemp=arraydeduplicate(lvtemp,cr())

lvtally = arrayfilter(lvtemp,cr(),|||aggregate({StateProvince},"count")|||)

produces 1500 (the total number of records in the file) as every result. Changing the final statement to:

 lvtally = arrayfilter(lvtemp,cr(),|||aggregate({import()},"count")|||)

gives the same result.


#2

I believe that’s what it’s supposed to do. According to the documentation the formula is ignored when you use the count option. What you want to do is use the query option.

lvtally = arrayfilter(lvtemp,cr(),|||aggregate({},"count",{import()=StateProvince})|||)

If the count method is used, the formula parameter is ignored, and can be left empty. (This is more useful when combined with the queryformula parameter described below.)

aggregate({},"count") ☞ returns the number of visible records in the database

By adding a query formula, you can restrict the aggregate to a subset of the database. This example calculates the total amount of all records that are more than 30 days old.

aggregate({Amount},"+",{Date<today()-30}) ☞ total of all records that are more than 30 days old
aggregate({Salary},"average",{Department="Engineering"} ☞ average salary of engineers
aggregate({},"count",{Salary > 2 * aggregate("Salary","average")})
☞ number of staff members that make more than twice the average salary

#3

I had already tried that without success -


#4

I have been working on this problem with Michael before he posted the question. I think the problem is that the import( function is not being evaluated when placed inside the formula of the aggregate( function which is inside the array filter function.


#5

Dave, did you get your example to work?


#6

No. I hadn’t even tried. I didn’t have a suitable database for testing. Your original question only mentioned one formula, and it was behaving exactly the way the documentation said it would, so I thought it was simply a case of misreading the documentation.