2 Replies Latest reply: Aug 6, 2014 5:30 AM by Michael Wienholt RSS

    Weighted average based on listbox selection

      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 CenterMonthAmountWeightCalculation: Amount * Weight
      ABC130,0000.515,000
      ABC210,0000.22,000
      Sum40,000

      n/a

      17,000
      XYZ1100,0000.110,000
      XYZ250,0000.525,000
      Sum150,000n/a35,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 CenterAmount * Weight
      ABC17,000
      XYZ35,000

       

      Month 1 is selected:

      Cost CenterAmount * Weight
      ABC15,000
      XYZ10,000

       

      Month 2 is selected:

      Cost CenterAmount * Weight
      ABC2,000
      XYZ25,000