2 Replies Latest reply on Jul 27, 2016 12:55 PM by Tae-Jin Kim

    compare non-aggregate to aggregate

    Tae-Jin Kim

      Let's say that I have a field of decimal values called Val.

      How can I create a new field that categorizes any values greater than the standard deviation of Val into "1" and rest into "2"?

      I tried:

           IF [Val] > STDEV([Val]) THEN "1"

           ElSE "2" END.

      But I get an error "cannot mix aggregate and non-aggregate arguments with this function."

       

      How can I get around this?

        • 1. Re: compare non-aggregate to aggregate
          Ashish Chaudhari

          Hi Tae,

           

          when you write [Val] it works at Row level and when you write Sum([Val]) it works as a aggregation. If you notice STDDEV is the aggregated function which require series of data and which can't be calculate over single value (cell).

           

          Thus as per your formula [val] is working at row level and STDEV([Val]) is working at the aggregated level which should not be the case. Comparisons are done at the either at row level or aggregated level. Your formula is mixing two things. Thus this formula thrown an erroe of "cannot mix aggregate and non-aggregate arguments with this function."

           

          Try doing

           

          IF Sum([Val]) > STDEV([Val]) THEN "1"

          Else "2" END.

           

          I hope now you are clear with this stuff.

           

          Thanks and Regards,

          -Ashish Chaudhari

          • 2. Re: compare non-aggregate to aggregate
            Tae-Jin Kim

            Hi,

             

            May I ask why you would compare Sum([Val]) to the STDEV([Val])?

            I actually am trying to compare individual values in Val to the standard deviation.

            For example, if I have values -1, 0, 1, 2 and a stdev of 0.5, then I want to -1, 0  to be "2" and 1, 2 to be "1".

            I tried what you have written above and actually all of the values are categorized to "2".

             

            TJ