# Qlik Sense App Development

Announcements
BI & Data Trends 2021. Discover the top 10 trends emerging in today. Join us on Dec. 8th REGISTER
cancel
Showing results for
Did you mean:
Highlighted
Contributor

## Fractile of weighted median with decimals

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

Labels (4)

0 Replies