5 Replies Latest reply on Jan 26, 2015 9:07 PM by philmodjunk

# My IF statement is misbehving

### Title

My IF statement is misbehving

### Post

Hi there

I'm looking to return the higher of two summarised fields in a third field using an IF statement to do it.  For example, If A>B then A, else B:

If (SummaryProfitA > SummaryProfitB; ProfitA;ProfitB)

To display this against all records and rows, I summarise this result itself.

Whatever I do, with or without the final summary part, it only ever returns A.

I've even pulled numerical results completely from the formula and replaced with text "High" and "Low" to check the concept and this only ever returns High.

FYI, I have data in SummaryProfitB which is higher than A, so this should be producing a different result!

Thanks

• ###### 1. Re: My IF statement is misbehving

Is this a calculation field?

Are the summary fields and this calculation field all defined in the same table?

What records are in your current found set?

Are you trying to calculated a single result for all records in your current found set or use different subtotals based on different groups of records within your found set? (grand totals or sub summary layout part based subtotals?)

• ###### 2. Re: My IF statement is misbehving

Hi Phil

Yes, this is set as a calculation field.

The summary and fields being summarised are all on the same table.

The found set has other summaries based on these same fields, so I would expect it to work, bearing in mind the figures have been used elsewhere.

I'm looking to calculate a subtotal, not the grand total.  There are several rows of data (created by the repeating sub summary per customer) and for each row, the IF statement should pick the higher of the previous two summary figures and place them in the column.

• ###### 3. Re: My IF statement is misbehving

Strange thing is, if I reverse the logic operator from < to >, I get the result I was after, but the other calculation results disappear from the sub summary.

Im wondering if this might be due to the second summary field on occasion being empty - there's not always data to sum up.

Not sure if this would count as zero, null, isempty or whether indeed the script would need protecting for this occurance.

• ###### 4. Re: My IF statement is misbehving

Sorted, I'm a numpty

• ###### 5. Re: My IF statement is misbehving

There are a few hand shaped dents in my face that fit that description too...