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 |
