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?
