Help...!!

[MK_QSL]

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

[tresesco]

Isn't it simple summation?

Dim: REGION

First Exp: Sum(CREDIT)

Sec Exp: Sum(SALES)

[MK_QSL]

No Dear, Customer is Repeating and hence SUM(Sales) coming right but the Credit also getting counted more than once...

[tresesco]

Could you be a bit more clear on expected output logic? Is it that you want only the unique amounts to be summed up?

[maxgro · Accepted solution]

sum(aggr(sum(distinct CREDIT), CUSTOMER))

[giakoum]

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

[ashfaq_haseeb]

Hi,

May be like this.

Regards

ASHFAQ

[MK_QSL]

Yes,  credit is repeating but need to count only once

[#9]

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)

[maxgro]