topic Re: Straight Table Getting all values except current line at aggregated level in QlikView

Straight Table Getting all values except current line at aggregated level

allan_vittrup — Mon, 09 Jul 2018 12:05:40 GMT

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?

Re: Straight Table Getting all values except current line at aggregated level

mayankraoka — Mon, 09 Jul 2018 15:22:15 GMT

Can you please attached sample qvw.

Regards,

Mayank

Re: Straight Table Getting all values except current line at aggregated level

vishsaggi — Mon, 09 Jul 2018 16:14:06 GMT

Why supplier Aqualung and Sports should get 1100?

Re: Straight Table Getting all values except current line at aggregated level

sunny_talwar — Mon, 09 Jul 2018 16:25:05 GMT

May be this

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

Re: Straight Table Getting all values except current line at aggregated level

allan_vittrup — Tue, 10 Jul 2018 07:30:59 GMT

Hi Sunny.

Exactly what I was looking for.

Thank you very much