Straight Table Getting all values except current line at aggregated level

[allan_vittrup]

Hi.

In a straight table I need to get the total revenue for each supplier, where the revenue is NOT generated for the current supplier for the current Doc.

In the final table, only the Supplier dimension is supposed to be there.

Note that, for Doc 3810 the result should be 0, because on that Doc there where to other suppliers.

Below is the sample data both in image and table form.

I need to go from this:                                                                               to This:

In table form:

From:

Doc	Supplier	Revenue
3810	Diverse	2755
3813	Nike	350
3813	Craft	550
3813	Aqualung	200
3813	Sports	200

To:

Supplier	Revenue	Revenue Not cur Supplier
	4055
Diverse	2755	0
Aqualung	200	1100
Craft	550	750
Nike	350	950
Sports	200	1100

I am assuming that I need some sum(aggr()) combined with a TOTAL but I haven't been able to create the correct expression.

Can anyone help?

[mayankraoka]

Can you please attached sample qvw.

Regards,

Mayank

[vishsaggi]

Why supplier Aqualung and Sports should get 1100?

[sunny_talwar · Accepted solution]

May be this

Sum(Aggr(Sum(TOTAL <Doc> Revenue) - Sum(Revenue), Doc, Supplier))

[allan_vittrup]

Hi Sunny.

Exactly what I was looking for.

Thank you very much