Calculation using sum/If

[Chrisf776]

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!

[Ender]

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.

[Chrisf776]

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.

[ryanp]

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.

 

Example:

 

Sample table LineItems

lineDate

c_calcDate

 

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.

Ryan

[Ender]

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.

[Chrisf776]

hey ender, thanks for your help. i just wanted to let you know i used your solution.