Weighted Calculation || Gewichtete Berechnung

[Enno86]

Hi Qlik-Experts,

I am new to Qlik Sense Business # and therefore a bit struggling with the possibilities provided by the tool :-

I have a field FST_ID which is an integer number (1 to 7) and assigned to each ID there is a limit value assigned, e.g. 1.2% up 2%.

Each of the FST_ID has a production volumne attached, which I am able to calculate in the charts using set expressions - one by one...

My goal now is to get the weighted limit value. In Excel it would be:  =SUMPRODUCT([Limit];T[volume])/SUM([volume])

Looking forward to any suggestions 🙂

Example 1:

Limit	FST_ID	volume
1,5%	1	10
2,0%	2	10
0,5%	3	0
	weighted:	1,75%

Example 2: 

LimitFST_IDvolume

LimitFST_IDvolume

1,5%	1	10
2,0%	2	10
0,5%	3	40
	weighted:	0,92%

[hic · Accepted solution]

If the two fields are in the same table, the following should work:

Sum(Limit*volume)/Sum(volume)

[Enno86]

Hi @hic , thanks for your reply.

They are in the same table, but are not unique.

Unfortunately the formula is off by more than factor 10. So far I wasn't able to get it to work.

Any more suggestions?

[Aditya_Chitale]

@Enno86 

Maybe Something like this ?

You can refer expressions in table column headers to understand better.

Regards,

Aditya

[Aditya_Chitale]

@Enno86 

The  difference you are getting is because you are multiplying limit and volume directly.

Instead, you will have to find the Limit(th) percentage of volume. For eg: 1.5(th) percent of 10 and similarly for other volumes. Then add  those derived percentages of each volume and divide by total of all Volumes.

I have derived Volume percentages in a separate column (4th column) from my screenshot. Hope this improve the understanding.

Regards,

Aditya