Weighted average based on listbox selection

Wed, 06 Aug 2014 09:21:05 GMT

Dear all

I want to calculate a weighted sum over multiple months and cost centers.

Assuming I have the following data my results would be 17,000 for cost center center ABC and 35,000 for cost center XYZ.

Cost Center	Month	Amount	Weight	*Calculation: Amount Weight**
ABC	1	30,000	0.5	15,000
ABC	2	10,000	0.2	2,000
Sum		40,000	n/a	17,000
XYZ	1	100,000	0.1	10,000
XYZ	2	50,000	0.5	25,000
Sum		150,000	n/a	35,000

In my QlikView report, I display the total amounts of all my cost centers in a straight table. I do not use the month as a dimension in my straight table, but rather as a filter in a list box.

My goal is to simply return the Amount * Weight by cost center based on the selected month in the Listbox. I found lots of example using aggregation functions, but all these examples assume that all data is available within the straight table (i.e. as a dimension or expression), but as I cannot show the month in my straight table I have no idea how I can solve my problem. Can anyone help me?

Thank you in advance.

Example how my output is supposed to look:

Month 1 and 2 are selected

Cost Center	*Amount Weight**
ABC	17,000
XYZ	35,000

Month 1 is selected:

Cost Center	*Amount Weight**
ABC	15,000
XYZ	10,000

Month 2 is selected:

Cost Center	*Amount Weight**
ABC	2,000
XYZ	25,000

Re: Weighted average based on listbox selection

tresB — Wed, 06 Aug 2014 09:24:37 GMT

Dimension: [Cost Center]

Expression: =Sum(Amount*Weight)

Re: Weighted average based on listbox selection

Wed, 06 Aug 2014 09:30:02 GMT

Ok, that was simple... thanks.

topic Re: Weighted average based on listbox selection in QlikView

Weighted average based on listbox selection

Re: Weighted average based on listbox selection

Re: Weighted average based on listbox selection