Dear all,
I've been trying and trying to figure this out (I read some many threads about it as well) but I can't handle my data to reach my goal.
Here is the story with a table (the calculation is made with excel):
I need to calculate the weighted average discount rate for all articles
| Article code | Public Price | Quantity | Sales |
| 163FG | 357,50 € | 5 | 1.148,50 € |
| 163FT | 370,83 € | 7 | 1.653,00 € |
| 163FU | 404,17 € | 8 | 2.163,30 € |
| 163FV | 537,50 € | 6 | 2.041,80 € |
| 163FW | 562,50 € | 3 | 1.077,74 € |
For each article, I've got public price, quantity and sales.
To get the discount rate I need to calculate the Average selling price as "Sales/Quantity"
| Article code | Public Price | Quantity | Sales | Average selling price |
| 163FG | 357,50 € | 5 | 1.148,50 € | 229,70 € |
| 163FT | 370,83 € | 7 | 1.653,00 € | 236,14 € |
| 163FU | 404,17 € | 8 | 2.163,30 € | 270,41 € |
| 163FV | 537,50 € | 6 | 2.041,80 € | 340,30 € |
| 163FW | 562,50 € | 3 | 1.077,74 € | 359,25 € |
Then I am able to calculate the discount rate for each article as "1-(Average selling price/Public Price)"
| Article code | Public Price | Quantity | Sales | Average selling price | Discount rate |
| 163FG | 357,50 € | 5 | 1.148,50 € | 229,70 € | 35,75% |
| 163FT | 370,83 € | 7 | 1.653,00 € | 236,14 € | 36,32% |
| 163FU | 404,17 € | 8 | 2.163,30 € | 270,41 € | 33,09% |
| 163FV | 537,50 € | 6 | 2.041,80 € | 340,30 € | 36,69% |
| 163FW | 562,50 € | 3 | 1.077,74 € | 359,25 € | 36,13% |
Final step to get the "Weighted Discount rate" I do :
=SUMPRODUCT(Discount rate;Quantity)/Sum(Quantity)
| Weighted Discount rate | 35,39% |
How would you do it within Qlik Sense? I am able to get a good discount for each article, when the total is not right !
That's my closest formula
((1-((Sum(Sales)/Sum(Quantity))/(Sum(Public Price))))*(Sum(Quantity)))/(Sum(Quantity))
I've enclosed the sample in a .xlsx file
Thank you in advance for your precisous help
