topic Re: AGGR/TOTAL - Lower Granularity Dimension in QlikView

AGGR/TOTAL - Lower Granularity Dimension

Anonymous — Thu, 17 Mar 2016 14:51:15 GMT

Hi,

I need to calculate a single value based on the following logic: If (Credit < Debit, Credit, Debit) aggregated on Customer Level.

Customer	Account	Debit	Credit
1	A1	100	0
1	A2	0	200
2	B1	500	450
3	C1	230	550

I'm using the following expressions (same result) in order to achieve this:

Expression 1:

sum(Total<Customer> RangeMin(Debit,Credit))

Expression 2:

=sum(aggr(RangeMin(Debit, Credit),Customer))

The value I'm expecting is 780:

Customer 1: 100

Customer 2: 450

Customer 3: 230

However, the result I get is 680, because Customer 1 has two accounts (A1 and A2), and instead of summing the up, QV is calculating them separately.

How can I achieve QV to ignore the account dimension and calculate 780 in sum?

Many thanks,

Paul

Re: AGGR/TOTAL - Lower Granularity Dimension

sunny_talwar — Thu, 17 Mar 2016 15:38:49 GMT

Try this:

Sum(Aggr(RangeMin(Sum(Total <Customer> Debit), Sum(Total <Customer> Credit)), Customer))

Re: AGGR/TOTAL - Lower Granularity Dimension

Anonymous — Fri, 18 Mar 2016 09:22:02 GMT

Hi Sunny,

thanks for your post! It's working!

However, If I add a filter, I'm getting the value 0.

=Sum(

aggr(

sum({<

Industry -={666}

>}RangeMin(Sum(Total<Customer>Debit), Sum(Total<Customer>Credit))

),Customer)

)

Re: AGGR/TOTAL - Lower Granularity Dimension

sunny_talwar — Fri, 18 Mar 2016 10:34:17 GMT

This?

=Sum({<Industry -={666}>}Aggr(RangeMin(Sum({<Industry -={666}>}Total <Customer> Debit), Sum({<Industry -={666}>}Total <Customer> Credit)), Customer))

Re: AGGR/TOTAL - Lower Granularity Dimension

Anonymous — Fri, 18 Mar 2016 12:44:41 GMT

Nice, thank you very much!