Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have table like below...
REGION | CUSTOMER | CREDIT | SALES |
A | A02 | 2700 | 100 |
A | A02 | 2700 | 200 |
A | A03 | 3000 | 200 |
A | A06 | 2700 | 400 |
A | A08 | 3000 | 600 |
A | A08 | 3000 | 800 |
A | A08 | 3000 | 300 |
SH | SH03 | 1500 | 200 |
SH | SH08 | 4000 | 600 |
SH | SH10 | 4000 | 800 |
SH | SH10 | 4000 | 1100 |
SH | SH20 | 1500 | 1200 |
SH | SH20 | 1500 | 1400 |
SH | SH25 | 3500 | 1600 |
Need output table as below
REGION | CREDIT | SALES |
A | 11400 | 2600 |
SH | 14500 | 6900 |
This solution should be on UI side only... I don't want to handle this on script end...
sum(aggr(sum(distinct CREDIT), CUSTOMER))
Isn't it simple summation?
Dim: REGION
First Exp: Sum(CREDIT)
Sec Exp: Sum(SALES)
No Dear, Customer is Repeating and hence SUM(Sales) coming right but the Credit also getting counted more than once...
Could you be a bit more clear on expected output logic? Is it that you want only the unique amounts to be summed up?
sum(aggr(sum(distinct CREDIT), CUSTOMER))
you can use the firstsortedvalue function to achieve that. If I understand well, records are repeated by credit, so you need sum of only one of the credit values
Hi,
May be like this.
Regards
ASHFAQ
Yes, credit is repeating but need to count only once
Dim REGION
Expr1: =sum(aggr(max(CREDIT),CUSTOMER))
Expr2: =sum(SALES)
You might need to add a column to identify which credit line to pick for each customer. Now it picks the one with largest value.
(Using max instead of distinct prevents summing two different credit values for the same customer if something is messed up in the data model)