Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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.
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?
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
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))