0 Replies Latest reply: Jan 2, 2012 3:00 PM by Tor Olav Alme RSS

    Table showing weighted averages in columns and rows?

      Dear Gents,

       

      Being very new to QlikView development, I am forced to ask for some help to solve a challenge. I hope and think it should not be very complicated; still, I am stuck...

       

      I have sales of a product. The comes in different size categories. In my excel setup, I have the different sizes in the columns. Sales volumes of each size category (product) is available in a variable in my report. Achieved price is available as turnover (variable) divided by sales volume. Reference price is joined in by an Excel file, one value for each size category of the product. Finally, achieved price is calculated as achieved price minus reference price.

       

      For each column (each size category) all these variable should be shown. On the far right column, there should be sums / weighted averages (using volumes as keys for averages).

       

      Finally, there will be many customers, and the total sum of the columns, should be weighted averages (by volume again) in each column.

       

      I have included an example file, showing how I used to do this in Excel (using a pivottable to present data from a dump from Business Objects).

       

      I am unsure whether to use a simple table or pivot to achieve the layout and content I am after.

       

      Very grateful for any advice / recipe to put me on track.

       

       

      Example layout, full report with many customers


      SIZE CATEGORY
      Customer NameData010020020030030040040050050060060070070000Grand Total
      Dummy 1Volume1 78537 779158 299383 938162 654

      744 455

      Achieved Price26,7033,1436,0836,6037,10

      36,40

      Reference Price24,7932,3436,3237,0538,18

      36,87

      Achieved vs Reference1,910,81-0,23-0,45-1,08

      -0,47
      Dummy 2Volume
      13 7921 520113 805338 132186 36285 452739 065

      Achieved Price
      29,7829,7636,6737,7742,4541,0438,99

      Reference Price
      29,5333,9336,2537,5140,0140,3438,12

      Achieved vs Reference
      0,25-4,170,420,262,440,700,88
      Dummy 3Volume
      9 54659 49458 224256 01276 34617 163476 785

      Achieved Price
      32,4735,0737,0139,2344,7845,0439,40

      Reference Price
      30,9234,9335,9337,5440,2240,5237,42

      Achieved vs Reference
      1,540,141,081,694,554,521,98
      Volume
      1 78561 118219 313555 967756 798262 708102 6161 960 305
      Achieved  Price
      26,7032,2835,7636,6638,1243,1341,7138,11
      Reference  Price
      24,7931,4835,9236,7737,6640,0740,3737,48
      Achieved vs  Reference
      1,910,79-0,16-0,110,463,051,340,63

       

       

       

      BR

      Tor Olav Alme

      Norway