topic Re: Multiply out matching rows in same table (Weighted Average) in QlikView

Multiply out matching rows in same table (Weighted Average)

Sat, 22 Oct 2016 16:48:30 GMT

Hi all, need some help please.

I have a single large table which contains a union of several Monthly data sections imported from Excel, e.g. Section B values represent number of switches of accounts, and Section C represents the average switching time of these accounts, I have 2 different values for Domestic and Non-Domestic.

I need to create a Pivot chart broken down by File Month, Supplier showing the average switching time for all accounts.

To achieve this I need to first of all multiply out the number of switches by the average switching time matching on Supplier and Region separately for both Domestic and non-domestic values, then sum the total of all these and finally divide by the total count of all switches.

I have attached a data sample.

Any help much appreciated!

Re: Multiply out matching rows in same table (Weighted Average)

sunny_talwar — Mon, 24 Oct 2016 06:02:39 GMT

What is the expected number you are hoping to see here? 2.62 for the sample? If this is right, then try this:

=(Sum({<Section = {B}>}[Non Domestic]) + Sum({<Section = {B}>}[Domestic]))

(Sum({<Section = {C}>}[Non Domestic]) + Sum({<Section = {C}>}[Domestic]))

Re: Multiply out matching rows in same table (Weighted Average)

Mon, 24 Oct 2016 09:14:07 GMT

Hi Sunny, thanks for the reply, unfortunately this isn't quite what I need, the value should be as in the sample 17.97, I need to multiply the individual Non Domestic and Domestic count values separately by the corresponding switching times and then sum them and divide by the total of both.

sum(

(Domestic Count * Switching Time (each row))

,(Non-Domestic Count * Switching Time (each row))

)

/Sum(Total count of (Domestic + Non-Domestic)

I have a feeling this may not be possible because its kind of a row by row calculation, and the Pivot table inly has Supplier as dimension,but not sure.

Many thanks!

Re: Multiply out matching rows in same table (Weighted Average)

sunny_talwar — Mon, 24 Oct 2016 14:45:09 GMT

I think it is possible, but you need to explain how you are coming up with these numbers?

If I can come up with these numbers, I can come up with a solution. Can you may be show the formulas behind these numbers? Right now they are seem to be pasted as values

Re: Multiply out matching rows in same table (Weighted Average)

Mon, 24 Oct 2016 15:36:58 GMT

Hi, apologies I pasted the wrong values in the spreadsheet, I've now amended it and kept the formulas in.

Hope it makes sense now.

Many thanks!

Re: Multiply out matching rows in same table (Weighted Average)

sunny_talwar — Mon, 24 Oct 2016 15:46:18 GMT

Try this:

=Sum(Aggr(RangeSum(

Sum({<Section = {C}>}[Non Domestic]) * Sum({<Section = {B}>} [Non Domestic]),

Sum({<Section = {C}>}[Domestic]) * Sum({<Section = {B}>} [Domestic])), [Supplier Name], Region))

RangeSum(

Sum({<Section = {B}>}[Non Domestic]),

Sum({<Section = {B}>}[Domestic]))

Here the assumption was that the Region was the most granular information which made each row unique within a particular section. If you have more granular information, replace Region with that more granular field name in the Aggr function (colored in red above)

Re: Multiply out matching rows in same table (Weighted Average)

Mon, 07 Nov 2016 11:12:06 GMT

Hi, sorry for delay, this seems to work fine, I was also thinking that using ONLY without RangeSum might work but this is great.

Thanks again!

Re: Multiply out matching rows in same table (Weighted Average)

sunny_talwar — Tue, 08 Nov 2016 01:48:53 GMT

If you have finally got what you needed, I would suggest you to close this thread by marking correct and helpful responses.

Qlik Community Tip: Marking Replies as Correct or Helpful

Best,

Sunny