topic Re: Aggr Sum Question in QlikView

Aggr Sum Question

sspringer — Wed, 03 Jul 2013 12:51:35 GMT

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 — Wed, 03 Jul 2013 12:53:26 GMT

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

sspringer — Wed, 03 Jul 2013 12:56:23 GMT

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 — Wed, 03 Jul 2013 13:00:01 GMT

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

sspringer — Wed, 03 Jul 2013 13:00:06 GMT

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