Hello Qlik community,
I'm relatively new to QlikSense and hope you can help me out with a problem I can't solve.
Following you can see an examplary excerpt of an aggregated table for a better explanation of my problem. The original data contains information on order line level.
I would like to calculate a fractile (e.g. 80%) on a weighted median of the Price (weighted by quantity). In this example we have 4 customers belonging to the same region, customer classification and buying the same product.
| SalesGroup | CustomerType | Channel | Subchannel | ProductID | Customer | Quantity | SumNetSales | NetSales/Unit: Price |
| Europe | A | Wholesale | Specialist | 1000100 | No. 1 | 204 | 11.579,50 | 56,76 |
| Europe | A | Wholesale | Specialist | 1000100 | No. 2 | 194 | 10.088,46 | 52,00 |
| Europe | A | Wholesale | Specialist | 1000100 | No. 3 | 1 | 54,30 | 54,30 |
| Europe | A | Wholesale | Specialist | 1000100 | No. 4 | 1 | 52,13 | 52,13 |
| … | | | | | | | | |
| Europe | B | Wholesale | Standard | 2000200 | No. 1 | 50,00 | 3.100,00 | 62,00 |
| ... | | | | | | | | |
For this combination of dimensions I created a new one to represent this cluster and as a base for my variables.
| Cluster | Quantity | SumNetSales | # Customers | PriceQuantile0.8avg |
| Europe-A-Wholesale-Specialist-1000100 | 400 | 21.774,39 | 4 | 55,28 |
PriceQuantile0.8avg takes only the 4 order lines (4 customers) into account instead of the weighted price including the volume. Now, the PriceQuantile0.8avg is based on following logic:
Top 20% price lies between the two highest price points: 56,76 ; 54,30
The difference is 2.46 and a price in-between would be 1.23 away from the upper and lower value. 1.23 x 0.8 = 0.984 and finally resulting in 54.30 + 0.984 = 55.28
In the ideal world, every unit sold would be represented by a single price point.
I found the qlik community entry "Weighted median" and tried the quite elegant formula presented, but it did not work because of the decimals in my case.
community.qlik.com/t5/QlikView-App-Development/Weighted-median/td-p/963962
RangeFractile(0.8,$(=
left(concat(repeat([Net Sales/Unit]&',',[Committed Battery Quantity])),len(concat(repeat([Net Sales/Unit]&',',[Committed Battery Quantity])))-1)
))
The idea was to copy each price as often as the quantity occurs and to use that chain as starting point for the rangefractile ().
But with the decimals the result looks like that :
50,57,52,00,52,00,52,00...,56,76,56,76 and my fractile gives me only 56,00 oder 76,00 as a result. The decimal digits are treated as integers. I also tried to change the formula and replaced ',' by ';' or '|', but then it did not work at all.
So, what I want to achieve as an outcome is that the 0.8 fractilce results in 56,76 in this example. A result that refers to an exact price point based on the historical data and not a fictional, calculated value.
I also found other examples with Aggr (... (sum(TOTAL weight)..., but in my case the weight variable (quantity) is defined by each single cluster and not over the whole dataset. In the end, there will be around 1.000 clusters and related fractile values.
I would be very grateful for any ideas to solve that problem (maybe simple by adjusting the RangeFractile formual from above)
Thank you very much in advance
Chris
