topic Re: Aggregating cost based on highest cost in QlikView

Aggregating cost based on highest cost

Anonymous — Wed, 16 Sep 2015 18:40:41 GMT

Hello,

I'm trying to sum the costs, per customer account, based on the highest single cost for that customer.

For example,

Account	Reason	Manager	Year	Purchase Count	Department	Cost	Code
023197775	Reason1	Manager1	2014	1	Dept1	167	1496369
023197775	Reason2	Manager1	2014	1	Dept1	48	1520254
023197775	Reason2	Manager1	2014	1	Dept1	1346	1525959
023197775	Reason3	Manager1	2014	1	Dept1	88	1548046
023197775	Reason4	Manager2	2014	1	Dept2	86	1591761
				5		1735

What I'm trying to get is 1 row with the total cost where the max cost is, per account:

Account	Reason	Manager	Year	Purchase Count	Department	Cost	Code
023197775	Reason2	Manager1	2014	1	Dept1	1735	1525959
				5		1735

Can anyone help with this?

Re: Aggregating cost based on highest cost

Clever_Anjos — Wed, 16 Sep 2015 18:48:05 GMT

Please check functions FirstSortedValue() and Max(), they should return what you need

Re: Aggregating cost based on highest cost

Oleg_Troyansky — Wed, 16 Sep 2015 18:49:37 GMT

This is so called Nested Aggregation, and it requires the use of the Advanced Aggregation function AGGR(). Essentially, you need to pre-aggregate your costs per Customer (possibly also per Reason, Manager, and Year) and then aggregate again and get the Max of these pre-aggregated numbers. So, something like this should work:

Max(

AGGR(

sum(Cost),

Account, Reason, Manager, Year

)

AGGR() is one of the least documented functions. I teach a half-day lecture at the Masters Summit for QlikView, just on AGGR() and Set Analysis. I also describe it in detail in my new book QlikView Your Business.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

Re: Aggregating cost based on highest cost

Clever_Anjos — Wed, 16 Sep 2015 18:52:20 GMT

PFA

Re: Aggregating cost based on highest cost

Anonymous — Wed, 16 Sep 2015 19:39:20 GMT

This works! It's unfortunate that the dimensions are no longer selectable but I'm working on putting those expressions in the load script. Thank you!