Jump to content
Salesforce and other SMB Solutions are coming soon. ×

Calculation using sum/If


Recommended Posts

I've been building an inventory/order database, but I'd like track spending in every fiscal year. Im trying to write a function to sum the total dollar amount from orders made between certain dates. However, i'm not sure how to get the If function to evaluate every record in a table (my line item orders table). The sum function seems to do this automatically, but the If function only evaluates the first record it finds even if i put it in the sum function. This is what i have so far:


Sum ( If (order_date ≥ Fiscal Year Start and order_date ≤ Fiscal Year End ; order_total ; 0 ))


Basically, i want to use the If function to check each order record for a date between the fiscal year start and end dates. If the date is within the fiscal year, i want the function to return the total price for that order. if not, i want it to return zero. After that, i want to sum the list of values created by If. I'd really appreciate any help/suggestions. I'm sure theres a premade function to do this, im just not sure what to use. Thanks a lot!

Link to comment
Share on other sites

You can't use a conditional within an aggregate function.


You'll need to add a table occurence that uses a date range filter to show only those line items between the two dates. Once the relationship is setup, you can use a simple Sum() on that relationship.


Adding date range filtering to a relationship is not too hard. You should first have two fields in the parent table to hold each date in the range. If these are not already data fields in your table, then add two Date fields and give them global storage (I'll call them gDateStart and gDateEnd). Now you can add a table occurence of Line Items (call it Line Items by DateRange or somthing) and link it by your order ID. Then double-click the resulting relationship line to bring up the Edit Relationship dialog. In the Edit Relationship dialog, add two more criteria, so that the relationship is defined like this:


Order Line Items by DateRange =

Order::OrderID = Line Items by DateRange::OrderID

AND Order::gDateStart

AND Order::gDateEnd >= Line Items by DateRange::OrderDate


The Total for that range is then:


Total by DateRange (calculation, number result) = Sum(Line Items by DateRange::Order Total)


When the dates are populated, the Total by DateRange will have its value.

Link to comment
Share on other sites

Thanks for your help! I might end up using that solution if i cant think of anythign else. It just seems strange that theres no way to write a conditional function/script that evaluates every record in a table and returns a value from the record if it is true and nothing if it is false.

Link to comment
Share on other sites

The way that I do this in a lot of my tables is to make an additional field such as c_calcDate which would simply be a calc field that populates itself with the field you are trying to match if it meets the criteria, or goes blank if it does not.




Sample table LineItems




c_calcDate would have your calculation in it:

if (lineDate YearBeginningDate; lineDate;"")


That way if it falls within the date range you specify, it will populate the date, if it does not, it won't. This gives you an extra field, but now in your layouts and relationships, where you would normally match by lineDate, you will now substitute c_calcDate, and your layout will show only those that you want shown.


Hope that is closer to what you were looking for.


Link to comment
Share on other sites

Unfortunately, such a calc would be unstored if the YearEndDate or YearBeginningDate is a global. And if they were not globals, they would have to be changed for every record whenever the user wants to change the date range. Such a change is not only a slow process on large record sets, but is problematic in multi-user environments.


In general, it's much easier to put global filters on the parent side of the relationship.


Chris, you can get similar results by scripting a find for the Line Items that fit in the date range, then using summary fields and sub-summary parts to show the totals.

Link to comment
Share on other sites

This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Create New...

Important Information

Terms of Use