Amount distributed per Dimension

[marcel_olmo]

Hi guys,

I have a table with sales per area and vendor like this :

Area	Vendor	Amount
Area1	Vendor1	2444 €
	Vendor2	3000 €
	Vendor3	5236 €
Area2	Vendor4	1629 €
	Vendor5	4828 €
Total		17138 €

And I'd like to create another field in the script which distributes the total amount between the total number of Vendors. In this case would be 17.138 € / 5 vendors = 3.428 €.

The desired result would be :

I've attached a dummy example of what I'm trying.

Regards, Marcel.

[sunny_talwar]

Check this out:

VendorAmounts:

LOAD Area,

    Vendor,

    Amount

FROM

VendorAmounts.qvd

(qvd);

Join(VendorAmounts)

LOAD Avg(Temp) as [Distributed Amount];

LOAD Area,

  Vendor,

  Sum(Amount) as Temp

Resident VendorAmounts

Group By Area, Vendor;

[sunny_talwar]

or you can use this in the front end without needing to do anything in the script:

Avg(TOTAL Aggr(sum(Amount), Area, Vendor))

[marcel_olmo]

Thanks for the try sunindia

I suppose that in layout would be easier than in script, but in this case it has to be like this.

The point is to get the same total but distributed equally per vendors, in your case, the only() function does not work for me because I need it inside a sum() function.

Regards, Marcel.

[sunny_talwar · Accepted solution]

May be this:

VendorAmounts:

LOAD Area,

    Vendor,

    Amount

FROM

VendorAmounts.qvd

(qvd);

Join(VendorAmounts)

LOAD Avg(Temp) as Temp1;

LOAD Area,

  Vendor,

  Sum(Amount) as Temp

Resident VendorAmounts

Group By Area, Vendor;

Join(VendorAmounts)

LOAD Area,

  Vendor,

  Count(Vendor) as Temp2

Resident VendorAmounts

Group By Area, Vendor;

Final:

LOAD Area,

    Vendor,

    Amount,

    Temp1/Temp2 as [Distributed Amount]

Resident VendorAmounts;

DROP Table VendorAmounts;

[marcel_olmo]

Great sunindia! That was it! Regards, Marcel.

[sunny_talwar]

Awesome