AGGR/TOTAL - Lower Granularity Dimension

[Anonymous]

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

[sunny_talwar]

Try this:

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

[Anonymous]

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)

)

[sunny_talwar · Accepted solution]

This?

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

[Anonymous]

Nice, thank you very much!