To find unique charges based on ID,REGION.

[hamzabob1]

Hello all,

I have one problem can u please help me out.

ID	TRANSACTION NO	REGION	TAX	VALUE
B1	T1	ABC	50	100
B1	T2	ABC	50	200
B1	T3	ABC	50	250
B2	T4	XYZ	25	500
B2	T5	XYZ	25	750
B3	T6	PQR	30	200
B3	T7	PQR	30	350

I need to calculate sum of VALUE w.r.t ID.

EX:  ID = B1

       VALUE = 550

As per the scenario the TAX will become 150, where as I need to consider it as unique(i.e., only 50 because my ID is B1 only)

so the final result should display as follows

________________________

ID: B1

Transaction count: 3

Region: ABC

TAX: 50

VALUE: 550

___________________________And also require Region wise as below

Region: ABC

TAX: 50

VALUE: 550

Region: XYZ

TAX: 25

Value:1250

_____________________________

Thanks in advance

[hamzabob1]

ID	TRANSACTION NO	REGION	TAX	VALUE
B1	T1	ABC	50	100
B1	T2	ABC	50	200
B1	T3	ABC	50	250
B2	T4	XYZ	25	500
B2	T5	XYZ	25	750
B3	T6	PQR	30	200
B3	T7	PQR	30	350
B4	T8	PQ	30	300
B4	T9	PQ	30	350

Above is my new table now.and my expected output is:

sum(TAX)=135

[kavita25]

You want this with addition to the above result?? or what??

[Anonymous]

Hi ,

Please elaborate what is the logic for sum(Tax) = 135 ??

[sunny_talwar]

Try this in a text box object:

=Sum(Aggr(Avg(TAX), ID))

[hamzabob1]

SUM(TAX)(50+25+30+30)=135

[kavita25]

Try this expression:

=sum(aggr(sum(distinct TAX),ID))

[sunny_talwar]

See the attached QVW:

[hamzabob1]

Thank you Sunny & Kavita that is wat i am looking for..

thanks again

[#10]

In straight table:

Dimension : ID,REGION,TAX

Expression1:  Aggr(Count([TRANSACTION NO]),REGION)

Expression 2: Aggr(DISTINCT Sum(VALUE),REGION)