product(array)
It is easy to get the sum() of multiple items in an array, such as a field in a selection of records, or one can easily get the max. or min. value of a field etc, but what is the best method to obtain the product of a field in a selection of records, i.e. items in an array?
The only way I currently see possible is to use a for… loop to iterate through that array and multiply each item/field. This does work, but I would think is a relatively slow way to do it when a built-in product() function would be so much quicker. Is there such a thing and simply not mentioned in the docs (wouldn't be the first omission)? Or are we relegated to using a slow loop?
8 replies
-
Yes you can use sum(array).
You can also do:
sum(array)
avg(array)
first(array)
last(array)
min(array)
max(array)
cnt(array) or count(array)It is in the manual: https://ninox.com/en/manual/calculations/reference-of-functions-and-language
Just have to scroll down far to find it.
-
If you have a selection of records and not just the data from 1 field, then you can do:
sum(arrary.fieldname) to find sum of the specific field.
-
Hi UKenGB
The join function will turn an array into a string and you can choose how to concatenate this. If you choode "*" then the resulting text field can be evaluated. Join uses text so any numbers will have to be turned into text first. This function on Table1 works well on a set of numbers in a child table Table2
eval(join(Table2.text(Number), "*"), this)
Regards John
-
Fred, how does sum() get me to the product of the all the fields in the array?
-
John, an interesting solution I had not considered. But in reality, is it faster than iterating through the items and multiplying by each one?
-
I'm not sure what you mean by "all the fields"? How many fields are there in each record? Why do they need to be added together outside of the record?
Thinking out loud, if you need to sum "all the fields" of all the records selected, then maybe you can first create a sum field of all the fields in each record. Then you can do a sum of that field at the next level?
-
I'd be interested to ask what the use case is as I've never had to find the product of a large set of numbers. I imagine it won't be the speed if iteration that will be the problem but just how big the answer becomes. Let's say we are finding the product of 20 numbers in an array. If the average value of each number is 5 the answer will be in the order of 5 to the power of 20!
Regards John
-
Ok, the reason is to calculate the overall ratio of a number of gear pairs. There will usually just be a single pair, but in some cases 2 and very rarely 3. So I need to first select the relevant records (easy), but then multiply the individual ratios returned (only one ratio per record). Since the number of gear pairs is variable (1 - 3) I need a 'function' that will work with a variable number of records and iterating through the selected records and multiplying the appropriate field from each, IOW, calculating the product of all values of that field in the selected set of records is how I'm currently doing it.
So it's the PRODUCT I need and not the SUM and although there may only be up to 3 records involved for each calculation, this calculation will have to be run for almost every record in the table and hence speed of execution will likely become an issue. Maybe John's idea using text and eval might be quicker than a for… loop. Either way, the quickest would be if there was a built-in product() function. I guess there isn't. :-(
Content aside
- 2 yrs agoLast active
- 8Replies
- 375Views