3 Replies Latest reply on Jan 7, 2017 1:32 AM by Chandra Bhaskar

    Parameter for Calculation

    Ben Hoa



      I am getting struck when i try to use LOD expression inside the IF statement.


      Error I am getting is cannot mix aggregate and non aggregate comparison in the IF statement


      I checked the blogs and i tried to use attr(field1) but it is also not helpful





      I need to calculate the difference of 2 numbers, based on the values  I need to do LOD


      if (avg(field1) - avg(field2) ) = 1 then


      { include field3:sum(sales)} else 0 end


      I need to include the field3 as field1 is derived from field3.


      If I didnt use avg in my IF statement, then the numbers are coming weird




        • 1. Re: Parameter for Calculation
          yogesh sawant

          Hello Ben,


          You can try converting the field from measure to dimention and then use in the calculation.


          Let me know if this doesnt resolve your issue.



          • 2. Re: Parameter for Calculation
            Ashish Chaudhari

            Hi Ben,


            is it possible to share the sample workbook here to know your case in detail and what is happening exactly.


            if (avg(field1) - avg(field2) ) = 1 then avg({ include field3:sum(sales)}) else 0 end


            Try using above formula. I have added avg function after then calculation. This is happening since in initial part you have used the avg() but in the function you didn't use any aggregation function (external). Thus it is giving the can't mix aggregate and non-aggregate values.


            Thanks and Regards,

            Ashish Chaudhari

            • 3. Re: Parameter for Calculation
              Chandra Bhaskar

              Hi Ben,,

              Try with given formula it should be helpful for you..


              { include field3: if (avg(field1) - avg(field2) ) = 1 then sum(sales) else 0 end }