Aggr Sum Question

[sspringer]

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!

[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.

[sspringer]

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?

[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

[sspringer]

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))