topic Re: Amount distributed per Dimension in QlikView

Amount distributed per Dimension

marcel_olmo — Wed, 10 Feb 2016 21:28:50 GMT

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

Re: Amount distributed per Dimension

sunny_talwar — Wed, 10 Feb 2016 21:40:29 GMT

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;

Re: Amount distributed per Dimension

sunny_talwar — Wed, 10 Feb 2016 21:43:25 GMT

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

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

Re: Amount distributed per Dimension

marcel_olmo — Wed, 10 Feb 2016 23:00:58 GMT

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.

Re: Amount distributed per Dimension

sunny_talwar — Wed, 10 Feb 2016 23:41:05 GMT

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;

Re: Amount distributed per Dimension

marcel_olmo — Wed, 10 Feb 2016 23:47:29 GMT

Great sunindia! That was it! Regards, Marcel.

Re: Amount distributed per Dimension

sunny_talwar — Wed, 10 Feb 2016 23:48:00 GMT

Awesome