4 Replies Latest reply: Jul 3, 2013 9:00 AM by Scott Springer RSS

    Aggr Sum Question

    Scott Springer

      I am having a very hard time with a data set.  It has approximately 20 columns.  The data repeats for a given invoice 2 or 3 times with 2 columns being different each time (invoice approver).  So we can have the following, this is an example:

       

      Company          Invoice          Approver          Amount

      ACME               1234               Bob               $100

      ACME               1234               Chris               $100

      EMCA               4321               Bob                $200

      EMCA               4321               Chris               $200

      EMCA               4321               Sheila               $200

       

       

      How do I show that AMCE spent $100 and EMCA spent $200 in a table using sums?

       

      Thank you!

        • Re: Aggr Sum Question
          Gysbert Wassenaar

          Use Company and Invoice as dimensions and avg(Amount) as expression. You could also use max(Amount) or min(Amount). All return the same value if the amounts are simply duplicated because of the approvers.

            • Re: Aggr Sum Question
              Scott Springer

              The problem is I have 200+ invoices for ACME, if I just do an average that won't work.  Perhaps some AGGR function to only average distinct invoices or rows of data?

                • Re: Aggr Sum Question
                  Gysbert Wassenaar

                  If you use Invoice as one of your dimensions the expressions will be aggregated per invoice. As long as one invoice has only one unique amount you get the correct result. You will only get wrong results if you have data like this:

                   

                  Company          Invoice          Approver          Amount

                  ACME               1234               Bob               $100

                  ACME               1234               Chris             $110

                  EMCA               4321               Bob               $200

                  EMCA               4321               Chris             $200

                  EMCA               4321               Sheila            $210

                   


                • Re: Aggr Sum Question
                  Scott Springer

                  Would a formula like this make sense to use?  I've created a "UNIQUE" field in the load statement that concatenates a set of fields that identifies a unique row of data?

                   

                  SUM(AGGR(AVG(Spend), Unique))