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 Name | Data | 010020 | 020030 | 030040 | 040050 | 050060 | 060070 | 070000 | Grand Total |
| Dummy 1 | Volume | 1 785 | 37 779 | 158 299 | 383 938 | 162 654 |
|
| 744 455 |
| Achieved Price | 26,70 | 33,14 | 36,08 | 36,60 | 37,10 |
|
| 36,40 |
| Reference Price | 24,79 | 32,34 | 36,32 | 37,05 | 38,18 |
|
| 36,87 |
| Achieved vs Reference | 1,91 | 0,81 | -0,23 | -0,45 | -1,08 |
|
| -0,47 |
| Dummy 2 | Volume |
| 13 792 | 1 520 | 113 805 | 338 132 | 186 362 | 85 452 | 739 065 |
| Achieved Price |
| 29,78 | 29,76 | 36,67 | 37,77 | 42,45 | 41,04 | 38,99 |
| Reference Price |
| 29,53 | 33,93 | 36,25 | 37,51 | 40,01 | 40,34 | 38,12 |
| Achieved vs Reference |
| 0,25 | -4,17 | 0,42 | 0,26 | 2,44 | 0,70 | 0,88 |
| Dummy 3 | Volume |
| 9 546 | 59 494 | 58 224 | 256 012 | 76 346 | 17 163 | 476 785 |
| Achieved Price |
| 32,47 | 35,07 | 37,01 | 39,23 | 44,78 | 45,04 | 39,40 |
| Reference Price |
| 30,92 | 34,93 | 35,93 | 37,54 | 40,22 | 40,52 | 37,42 |
| Achieved vs Reference |
| 1,54 | 0,14 | 1,08 | 1,69 | 4,55 | 4,52 | 1,98 |
| Volume |
| 1 785 | 61 118 | 219 313 | 555 967 | 756 798 | 262 708 | 102 616 | 1 960 305 |
| Achieved Price |
| 26,70 | 32,28 | 35,76 | 36,66 | 38,12 | 43,13 | 41,71 | 38,11 |
| Reference Price |
| 24,79 | 31,48 | 35,92 | 36,77 | 37,66 | 40,07 | 40,37 | 37,48 |
| Achieved vs Reference |
| 1,91 | 0,79 | -0,16 | -0,11 | 0,46 | 3,05 | 1,34 | 0,63 |
BR
Tor Olav Alme
Norway
