5 Replies Latest reply: Feb 21, 2018 5:21 PM by Wanyun Yang RSS

    compare the two functions

    Wanyun Yang

      Hi guys,


      I'm confused by these two functions:

      SUM(Aggr(SUM(Amount), Group))

      SUM(Amount)/COUNT(DISTINCT Group)


      What is the difference between them?


      Thanks in advance!

        • Re: compare the two functions
          Ruben Marin

          Hi Wanyun, the first one is doing an addition of the Amounts of each group.

          The second it's doing all the amount divided by the different number of groups, like an average of amount per group.

          • Re: compare the two functions
            Digvijay Singh

            If you have following values -


            Group  Amount

            A 100

            B 500

            C 400


            The above table is virtual output of Aggr(Sum(Amount),Group) as well, if you surround it with one more SUM as  SUM(Aggr(SUM(Amount), Group)), its output is 1000, means sum of all Amounts of virtual table you see above.

            If you use SUM(Amount)/COUNT(DISTINCT Group) in text box then it will result into - 1000/3 (we have 3 distinct group)

            If you use this expression in chart with Group as dimension then each row will have its Amount/1( each row has one count only)

            So same expression show different results based on where exactly its being used, in text/image boxes or in the chart, different dimensions will show different results. Use of TOTAL inside expressions as well make difference as it indicates at which dimension level the aggregation is being performed.

            Hope it helps!