0

FORMULA RUNNING BUT NOT PRODUCTING RESULTS

Laura Porter

December 17, 2019 19:55

None

Follow

Hi:

 

I tried running a calculation formula as a filter.  The report runs but does not produce any results - i know there should be results.  Here is my statement:

 

=NL("Rows","Item Ledger Entry","Item No.","Item No.",$C$3,"=(NF(,""Sales Amount (Actual)"")+NF(,""Cost Amount (Actual)""))/NF(,""Sales Amount (Actual)"")",$C$4,"Posting Date",$C$5,"Entry Type","Sale","Sales Amount (Actual)","<>0")

C4 is an amount ie:  <.2 - this is a below margin formula looking for transactions below margin.  Is this possible?  Or is there a better way to accomplish this?  Any help would be appreciated!

thanks,  Laura

 

 

• Facebook
• Twitter
• LinkedIn

8 comments

• 0
  
  Harry Lewis December 17, 2019 21:08

  Hi Laura -

  You may want to ensure that cell C4 is set to <0.2

  

   

  instead of <.2

  

• 0
  
  Laura Porter December 20, 2019 20:06

  Thanks Harry!  This definitely worked!  But I have a follow up question... Is it possible to make that filter a Sum?  this part

  "=(NF(,""Sales Amount (Actual)"")+NF(,""Cost Amount (Actual)""))/NF(,""Sales Amount (Actual)"")",$C$4,"Posting Date",$C$5,"Entry Type","Sale","Sales Amount (Actual)","<>0"

   

  The problem with my statement is that it is looking at individual transactions and not the sum of all transactions for that period.  Is this even possible?

  thanks again for your help!

   

• 0
  
  Harry Lewis December 20, 2019 22:40

  You can.

  You've got most of what you need, already.  I assume what you want is:

  Instead of showing all transactions from the Item Ledger Entry table, you want to show each Item number from that that where SUM(Sales Amount - Cost Amount) / SUM(Sales Amount) is unequal to zero (or greater than or less than or some other number).

  This involves the same concept as the Sort by Sum with Dynamics NAV.

  I hope that helps.

  -Harry

  Reports Your Way

• 0
  
  Laura Porter December 23, 2019 21:09

  Thanks Harry :)   This is what I came up with but when I tried to get it in quotes it says "invalid field" when I try to run it.

   

  ="NL(""Sum"",""Item Ledger Entry"",""=(NF(,""Sales Amount (Actual)"")+NF(,""Cost Amount (Actual)""))/NF(,""Sales Amount (Actual)"")<"""&$C$4&""""",""Entry Type"",""Sale"",""Sales Amount (Actual)"",""<>0"",""Posting Date"","""&$C$5&""",""Item No."","""&$C$3&""")"

   

  I did get a 0 value before I put it in quotes.  And I used the Sort by Sum method of $C$10 "*" for the filter in the item statement.  Not sure what I did wrong :(

• 0
  
  Laura Porter December 23, 2019 21:19

  Also i tried changing Item No. filter to NF(, "Item No.").

   

  ="NL(""Sum"",""Item Ledger Entry"",""=(NF(,""Sales Amount (Actual)"")+NF(,""Cost Amount (Actual)""))/NF(,""Sales Amount (Actual)"")<"""&$C$4&""""",""Entry Type"",""Sale"",""Sales Amount (Actual)"",""<>0"",""Posting Date"","""&$C$5&""",""Item No."",NF(,""Item No.""))"

   

  Still get an Invalid Field :(

• 0
  
  Laura Porter December 23, 2019 21:41

  Update how silly of me -- missing the = sign at the beginning of statement!  Working now!!!

• 0
  
  Laura Porter December 23, 2019 21:51

  Unfortunately it errored out saying the text 0.18 cannot be parsed.  thtat is my $C$4 Value - is there a way to let it know that it is a number?

• 0
  
  Laura Porter December 23, 2019 22:21

  I tried <NUMBER&"""&$C$4&""" and it still came up with the same error - "text 0.18 cannot be parsed" :(  I'm at a total loss now - have tried all the tricks in my bag.