Conditional Sum functions

[simulacra]

I am trying to create a report that will create sums of product sales information (# sold, gross sales, total profit) in two columns. In the first column, I would like to return data form a range of one month as defined by a SUBMONS function; in the second column the same month but one year prior using a SUBYRS function based on the dates in the first column. I have already created the fields that will return the desired dates in the header. Now I am trying to tailor SUM fields to only return results from the date range at the top of the column. I tried to enter several variations of IIF(INV_DATE=INRANGE(date1, date2)) under the "Condition" tab of the Sum properties, but it always returned the "Required Argument" error. It seems like I will have to use something like this since I can^t just query the whole report by the date ranges if I want the results from different years to remain separate. Any ideas?____

[kfleming]

Your condition needs to evaluate to a boolean value.____You could use:__INV_DATE>=date1 and INV_DATE<=date2____to total only those records whose INV_DATE falls between date1 and date2____Kathleen__R&R Support

[simulacra]

Thanks for the help. That did succeed in returning the proper date ranges, but there are still a few issues remaining.____The report is set up so that only the page header and group footer are displayed. I have my sales list grouped according to ITEM, and in the ITEM footer I have the following: ____ITEM, __DESC, __Sum for # sold in date range 1, __Gross sales for date range 1, __Gross profit for date range 1,__Profit margin (as %) for date range 1,__Sum for # sold in date range 2,__Gross sales for date range 2,__Gross profit for date range 2,__Profit margin (as %) for date range 2____The first problem is that, with the way MOM accounts for returned items, the "return" tag only prints to the credit for the returned item and not to the original sale of the item. As a consequence, even if I have the # Sold field calculated so that it does not count lines with the "return" tag, the initial sale that should be nullified on account of the return is still being counted. I could imagine how to write a macro in VBA that would take care of this, but not in R&R. The other issue is that some of the values in the record band for date range 2 are just plain wrong, and I don_t know where those wild values are coming from. They are all derived just like those in date range 1. I suppose this amounts to a lot of questions with not much solid information to go on, but I_m just stuck. Thanks for taking the time to read this and for any advice.__

[kfleming]

Sorry for the delay in reply.__Are you still having problems with this report?____Kathleen__R&R Support

[simulacra]

The issues mentioned above have been worked out, but there is one lingering problem. It is not entirely clear how to remove returned items from the list, as MOM has a separate line entries for the original purchase and the return. Given that the gross sales field is calculated using quantity * list price, I^m thinking that a calculated quantity along the lines of IIF(RCODE="B", -1, 1)would work. Am I on the right track, or is there a better or more elegant way of working around this particular annoyance in MOM^s structure?

[kfleming]

I think that is the correct approach.____Kathleen__R&R Support

[Quick Loan]

I always calculate gross quantity shipped this way:____I make a calculated field to determine whether to assign a positive or negative quantity to the line item, depending on whether it was a return or a sale:____Name the calculated field "quants" with the following expression:____iif(isblank(r_code)<>.t. or item_state="RT",-quants, quants)____You could use "Quanto" instead, if you^re calculating items ordered rather than shipped.____Then, when you add down, add "Realquants" instead of just Quants with a condition, and you^ll always end up with the appropriate gross number sold.