I am needing to create a sum of the product of two fields in a table. For Example: I want to sum the product ("Field 1" x "Field 2") of all the entries from a specific table. Is it possible? I've tried putting NL statements withing NLs and haven't found any solution. It seems simple and should be possible but I haven't been able to find any examples of how it would be done.
Thanks
Curtis
Date
Votes
5 comments
-
Jet Reports Historic Posts Hi Curxis,
You are very close… Give this one a try: =NL("Sum","Table A","=NF(,""Field 1"")*NF(,""Field 2"")")
I'm doing this by heart - please let us know if it works for you.
Kind regards from the European side of the Big Pond -
Jet Reports Historic Posts SWEET! :D
Thanks hansfousert, I figured it was possible but couldn't think how it would be done. Your solution worked perfectly. Did take me a little time to get it right as I was nesting it inside another NL statement.
Thanks Again. -
Jet Reports Historic Posts Just to be curious, what fields of what table were it?
Often the field is found directly in the table… -
Jet Reports Historic Posts That works well. I am trying to do something similar but where the second term in the SUMPRODUCT caclualtion is from a different table. See below for the formula with two fields from the main table being used (I have used Quantity field twice to check it works etc.
=NL("Sum","Business Written Store","=NF(,""Quantity"")*NF(,""Quantity"")","Responsibility Center",$G$4,"Original Document Date",H$3,"Link=","Item","No.","=No.","Product Group Code",$C23)
I now need to amend this so that the second NF(,""Quantity"") is replaced by something that uses the UNIT PRICE field from the Item table so I can return the total Full value sales value of the items in the Business Written table. (it only holds the actual sales achieved, not whhat they would have been at full price….). The ITEM table is already linked int he formula as I am setting this to calculate for specific Product Group Codes only already so hopefully that may help….?
Any suggestions gratefully received. -
Jet Reports Historic Posts Hi,
You can probably do something like this:=NL("Sum","Business Written Store","=NF(,""Quantity"")*NL(""Sum"",""Item"",""Unit Price"",""No."",NF(,""No.""),""Product Group Code"","""&$C23&""")","Responsibility Center",$G$4,"Original Document Date",H$3,"Link=","Item","No.","=No.","Product Group Code",$C23)
I'm not sure if you need a sum or just a first there (depends on whether there are multiple unit prices in the item table for each entry on the business written store table). Also, it seems a little odd that the No. field of the Business Written Store table is actually storing item numbers. I suppose this is a custom table though, so anything is possible. Does something like that work for you?
Regards,
Hughes
Please sign in to leave a comment.