Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try this
=Sum(Aggr((1 - (Sum(Sales)/Sum(Quantity)/Sum([Public Price]))) * Sum(Quantity), [Article code]))
/
Sum(Quantity)
Try this
=Sum(Aggr((1 - (Sum(Sales)/Sum(Quantity)/Sum([Public Price]))) * Sum(Quantity), [Article code]))
/
Sum(Quantity)
You're a god ! It's working.
Good news for the coming weekend 🙂
Do you know why my attemp was not working and that I needed to aggr on the article (as my table with filtered on article)?
Because for the total row... you wanted to sum the multiplication of discount rate and quantity... you need to sum them otherwise the total row will also be done as any other row.