0

Sum the results of calculating 2 fields together in a record

Jet Reports Historic Posts

August 11, 2008 11:50

None

Follow

Hi all,
in: http://support.jetreports.com/index.php?_m=knowledgebase&_a=viewarticle&kbarticleid=493, labeled "Sum the results of calculating 2 fields together in a record" is described what seems to be of great use to me, calculating a difference without needing to extract all rows individually (and then have excel calculate the lines). However, I can't get this to work with date fields, is that correct or am I just doing it wrong? What I need to achieve is average days to pay, but if possible without first retrieving tens of thousands of lines. Ideally, I'd type in something like NL("Sum", "Vendor Ledger Entry", NF(;"Closed at Date")-NF(;"Posting Date")) but like I said, I can't quite get the right syntax…
Thanks in advance for any tips,
Chris

• Facebook
• Twitter
• LinkedIn

7 comments

• 0
  
  Jet Reports Historic Posts August 11, 2008 14:36

  I think you need something like this
  
  =NL("SUM","Vendor Ledger Entry","=NF(;""Closed at Date"")-NF(;""Posting Date"")")
  You have to put in all the extra quote marks.

• 0
  
  Jet Reports Historic Posts August 13, 2008 13:57

  Tried it, but didn't work I'm afraid… Can get the KB example to work (sales line, qty*price) but substituting the field names with date fields makes it fail. Am I missing something (conversion step?) or is it just not supported?

• 0
  
  Jet Reports Historic Posts August 13, 2008 14:33

  Hi Chris,
  
  I did a small test on my Cronus database and this is what I found… Creating a Unique by comparing 2 date fields work and listing the result in rows works, but the NL(Sum) gives me 0. The report is attached.
  
  Maybe the Jet Reports Support dept. or developers could help us here and tell us what's happening here?

  • Example of Unique and SUM on Dates.xls
• 0
  
  Jet Reports Historic Posts August 13, 2008 17:10

  Continuing where I left this afternoon…
  
  Looking for another way to calculate the average no. of days… if we have the unique differences… we could create a COUNT to count how many entries had the same difference…
  
  So I entered =NL("Count";"Cust. ledger entry";;"Entry no";"=IF(NF(;""Due date"")-NF(;""Document date"")=CellRefToUniqueFunction;NF(;""Entry no""))").
  
  But unfortunately I got this CF error 1246189 :? . Does anyone have a clue what this error means?

• 0
  
  Jet Reports Historic Posts August 14, 2008 06:31

  Hi Hans,
  I may have told you I'm a fast learner - but this seems to be a tad over my head for now ;) The Sum resulting in 0 is what I came up with, too in the end (after many, many #VALUE!'s), so no luck there. I'd be really interested if anybody can shed some light on this.
  Chris

• 0
  
  Jet Reports Historic Posts August 15, 2008 15:29

  Hi everyone.
  
  I've tested a the following examples from Hans' workbook
  =NL("Rows";"111 Sales Shipment Line";;"Limit=";"10") =NL("Rows";"111 Sales Shipment Line";"=NF(;""5794 Planned Delivery Date"")-NF(;""5795 Planned Shipment Date"")";"Limit=";"10") =NL("Sum";"111 Sales Shipment Line";"=NF(;""5794 Planned Delivery Date"")-NF(;""5795 Planned Shipment Date"")";"Limit=";"10") and for me, all 3 formulas worked correctly. However, you may not be getting the expected results. There are some differences in how "Limit=" works between NL("Rows","Table","Field") and NL("Rows","Table") and NL("Sum")
  
  First, let us look at
  =NL("Rows";"111 Sales Shipment Line";;"Limit=";"10")This formula returns the first 10 records in the "111 Sales Shipment Line" table
  
  =NL("Rows";"111 Sales Shipment Line";"=NF(;""5794 Planned Delivery Date"")-NF(;""5795 Planned Shipment Date"")";"Limit=";"10")This formula does something a little different. It reads all of the records in the "111 Sales Shipment Line" table, performing the calculation on each record, and creates a list of the resulting values, removes the duplicates, sorts the list, and finally returns the first 10 values from the list.
  
  =NL("Sum";"111 Sales Shipment Line";"=NF(;""5794 Planned Delivery Date"")-NF(;""5795 Planned Shipment Date"")";"Limit=";"10")This formula has an additional complexity to it. In this formula, you are reading all of the records in the table, performing the calculations on each record, creating a list of the resulting values, not removing the duplicate values, sorting the list, and summing the first 10 values in the list.
  
  You can see the records that are used in the NL("Sum") example by using the following formula
  =NL("Rows";"111 Sales Shipment Line";;"+=NF(;""5794 Planned Delivery Date"")-NF(;""5795 Planned Shipment Date"")";"*";"Limit=";"10")
  Hopefully I haven't confused the matter more. It took me some time (and talking to one of the development team) to understand why this was happening.

• 0
  
  Jet Reports Historic Posts August 22, 2008 10:06

  Dear Sherman,
  Thanks for your explanation - I got it working now. Honestly, I thought I already tried what I came up with but apparently I continuously had some typing error.
  Should anybody be interested, my function became:
  –
  =NL("Som";"Vendor Ledger Entry";"=NF(;""Closed at Date"")-NF(;""Document Date"")";"Document Type";"Invoice|Credit Memo";"Closed at Date";"01-01-2000.."&TEKST(EndDate;"dd-mm-jjjj");"Vendor No.";NL("Filter";"Vendor";"No.";"Gen. Bus. Posting Group";"MC");0;CompanyName)
  –
  (divided by nl(count) with the same parameters). :)