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

    My IF statement is misbehving



      My IF statement is misbehving


      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!



        • 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...wink