x Want to participate? Join the group to interact
  • State Suggested Answer
  • Replies 1 reply
  • Answers 1 answer
  • Subscribers 15 subscribers
  • Views 282 views
  • Users 0 members are here
Browse Forums
Announcements
105
topics
4
replies
General Discussion
1k
topics
2k
replies
Mark Dee

SumIf not giving correct result

SUGGESTED
Posted By

Hi all,

I've been trying to create a simple SumIf expression, which references an expression earlier in the report that I've created to add a small value of 0.50 to invoiced items that come in at 0 value. Here's the referenced expression:

SOPInvoiceCreditLines.LineTotalValue = 0 ? (SOPInvoiceCreditLines.LineTotalValue + 0.50) * SOPOrderReturnLines.LineQuantity : SOPInvoiceCreditLines.LineTotalValue * 1

This works correctly and is fine, but the SumIf I created near the bottom of the report is just looking to get all instances of this same situation.
SumIf(Expr18, SOPInvoiceCreditLines.LineTotalValue=0)
I just can't quite get the values to align correctly, however. Initially it was too high, so I've tried setting the evaluate section to only be the section of this top expression, currently called Expr18, but that's only made it incorrect too low.
  • 0
    SUGGESTED

    Morning Mark,

     

    Thanks for using the Sage 200 Sage City Community.

     

    If you are wanting the initial expression to be calculated on the SOP line Qty, you would need to use the expression: SOPInvoiceCreditLines.LineTotalValue = 0 ? (SOPInvoiceCreditLines.LineTotalValue + 0.5) * SOPOrderReturnLines.LineQuantity : SOPInvoiceCreditLines.LineTotalValue * 1

     

    But if you are wanting the expression to be based on Invoice Line Qty, you would need to use the expression: SOPInvoiceCreditLines.LineTotalValue = 0 ? (SOPInvoiceCreditLines.LineTotalValue + 0.5) * SOPInvoiceCreditLines.InvoiceCreditQuantity : SOPInvoiceCreditLines.LineTotalValue * 1

     

    Now in regards to the totals at the bottom, if you are just wanting the total to be 0.00 item lines only, then you would need to use the expression: SumIf(Expr18,SOPInvoiceCreditLines.LineTotalValue=0) and evaluate the section to the section were the initial expression is placed. If the expression to be of all items lines, you will just need to build an expression looking at the initial expression then evaluating it on the header/footer were the initial expression is.

     

    If you are still having further issues after following the above, if you a business partner, you will need to raise a case with us via case management and we can investigate this further for you, please see link below on how to raise a case.

     

    Raise a new case via Case Management

    https://gb-kb.sage.com/portal/app/portlets/results/viewsolution.jsp?solutionid=200427112340794

     

    If you are a 200 customer and still having issues, you will need to book an appointment with our Report Design Service, where they will be able to look in to designing a working expression for you, please see link below on how to book and appointment, this service is part of your support contract and your are entitled to 3 free report designs per year.

    Sage 200 Report Design Service

    gb-kb.sage.com/.../viewsolution.jsp

     

    Please can you mark this thread as being verified by clicking More and Verify Answer. This will show the community this is the correct process to follow if they encounter similar issue in future with their Sage 200 program.

     

    Regards,

    Dale

*Community Hub is the new name for Sage City