5 Replies Latest reply on Jun 21, 2017 7:02 AM by Mark Fraser

    Issue with IF THEN

    jasmine.ellison

      Hello,

       

      I am trying to use this calculation but there is an error because "Expected "Then" to Match "IF" statement at character 37"

       

      IF [Larceny- theft rate] < 1775.4 OR IF [Sheet2 (Juvi)].[Larceny] < 652 THEN "An Alarm System is Not Required"

      ELSEIF [Larceny- theft rate] >= 3551 OR IF [Sheet2 (Juvi)].[Larceny] >= 1304 THEN "A Full Security System is Suggested"

      ELSE "An Alarm System Is Strongly Suggested" END

       

      Any suggestions will be appreciated. Thank you.

        • 1. Re: Issue with IF THEN
          Mark Fraser

          Hi Jasmine

           

          From a quick look i don't think you need to reference IF again.... so

           

          IF [Larceny- theft rate] < 1775.4 OR [Sheet2 (Juvi)].[Larceny] < 652 THEN "An Alarm System is Not Required"

          ELSEIF [Larceny- theft rate] >= 3551 OR [Sheet2 (Juvi)].[Larceny] >= 1304 THEN "A Full Security System is Suggested"

          ELSE "An Alarm System Is Strongly Suggested" END

           

          let me know - in the mean time, i'll double check!

           

          Cheers

          Mark

          • 2. Re: Issue with IF THEN
            Mark Fraser

            the example is nonsense, but i hope it shows how to structure the function

            also... you may find you need to wrap [Larceny- theft rate] in SUM() so SUM([Larceny- theft rate])... i don't know if your data is aggregated or not?!

             

            hope that helps

            Mark

            • 3. Re: Issue with IF THEN
              jasmine.ellison

              Hello,

               

              Thanks for the tip. And I get new error "All fields must be aggregate or constant when using Table Calculations or fields from multiple data sources"

              • 4. Re: Issue with IF THEN
                Mark Fraser

                i was expecting that... errors are layered - so as we solve layer 1 we get the next error

                 

                the new error - basically your mixing aggregates, and non aggreates

                as an example - you maybe comparing days vs month - which doesn't work

                 

                normally you can wrap the non-aggregated values in SUM() to aggregate and ensure the levels are the same

                alternatively you use the ATTR function to dis-aggregate the aggregated data

                either way - the granularity needs to match

                 

                i don't know your data but that's what i was referring to when i said... i suspected this may happen!

                also... you may find you need to wrap [Larceny- theft rate] in SUM() so SUM([Larceny- theft rate])... i don't know if your data is aggregated or not?!

                 

                are you able to provide an example? otherwise do as i suggest - but do it in steps... then you know when you have fixed it!