Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
l_smythe80
Partner - Contributor II
Partner - Contributor II

Finding the middle 50% of range and median price paid

HI all,

looking for some help on a complex piece of logic that im struggling to find an answer for..

i am trying to find where the middle 50% range sits of the quantities purchased across all customers and of these middle 50% to then find where the count of min price paid is greater than 55% (previously answered by Sunny T with thanks), of their min prices paid and then from all these customers who fall into this logic (example shows customer 3,4 as qty in middle 50% and min price % 60,80%), then work out what target mode price would be for these 2 customers to create a realistic achievable price to be used for all other customers regardless of their price to calculate a guide price in which is achievable by all customers buying the same item.

i have attempted to draw it up in excel, the top table is already aggregated in a straight table to a customer level and the min price and count of min price logics working (thanks sunny). bottom table then shows the raw details which shows all prices paid by customer 3,4 of which we can work out a target price.

Screenshot 2019-11-05 at 15.13.54.png

i hope this is achievable , and if any further info is required, please feel free to ask or message and thanks in advance

 

Labels (4)
17 Replies
sunny_talwar

2 things

1) Sorting for ranking was changed...

2) Amount was not read as a number

Try attached

l_smythe80
Partner - Contributor II
Partner - Contributor II
Author

absolutely superb... legend and timely as always

l_smythe80
Partner - Contributor II
Partner - Contributor II
Author

Hi Sunny,

this works perfectly, but do you know if there is anyway to set the Median Price across lines (have done by adding total before the aggr function, but ideally i want this same value to show for all customers no matter if a customer is selected?

so where the value is 214 as a median price, if i select a dozen customers i would still want this to equate to 214 and not re-calculate

 

any ideas would be welcomed thank you

sunny_talwar

I have updated the expression to 

Median({<Customer>}TOTAL Aggr(
  If(
    RangeSum(Above(Sum({<Customer>}Quantity), 0, RowNo()))/Sum({<Customer>}TOTAL Quantity) > 0.4 and
    RangeSum(Above(Sum({<Customer>}Quantity), 0, RowNo()))/Sum({<Customer>}TOTAL Quantity) <= 0.75 and
    Min({<Customer>}%MinPriceExpression) > 0.55
  , Only({<Customer>}MinPriceExpression))
, (Customer, (=Sum({<Customer>}Quantity), DESC)))) * Avg(1)
l_smythe80
Partner - Contributor II
Partner - Contributor II
Author

me again 🙂 haha

although this seems to work on the pre-aggregated app that i sent as a demo, doesn't seem to like the logic on the app which uses raw line detail as opposed to aggregated.

not sure if its an issue at point in which it calculates the %minpriceexpression or the minpriceexpression currently the %minpriceexpression  is:

Count(DISTINCT Aggr(
    If(PricePaid = Min(TOTAL <Customer> PricePaid), [Order Line])
, Customer, [OrderLine]))
/
Count(DISTINCT [Order Line])

 

and the minpriceexpression is this:

Min(PricePaid)

 

so not sure how i could fit these into the code you sent below

 

Median({<Customer>}TOTAL Aggr(
  If(
    RangeSum(Above(Sum({<Customer>}Quantity), 0, RowNo()))/Sum({<Customer>}TOTAL Quantity) > 0.4 and
    RangeSum(Above(Sum({<Customer>}Quantity), 0, RowNo()))/Sum({<Customer>}TOTAL Quantity) <= 0.75 and
    Min({<Customer>}%MinPriceExpression) > 0.55
  , Only({<Customer>}MinPriceExpression))
, (Customer, (=Sum({<Customer>}Quantity), DESC)))) * Avg(1)

 

l_smythe80
Partner - Contributor II
Partner - Contributor II
Author

@sunny_talwar  i Have added QVW of row level detail instead of the pre-aggregated excel version for reference.

i'm struggling to make the straight table retain the Min value for target min price by ignoring any customer selection. so if any selections are made in customer column it still needs to retain the 214.5 if possible.

thanks in advanced, this has been a real challenge so far

 

sunny_talwar

Try this

min({<Customer>}TOTAL Aggr(
	if(rank(sum({<Customer>}Quantity),4,1)	> fractile({<Customer>}TOTAL aggr (rank(sum({<Customer>}Quantity),4,1),Customer), 0.15) and 
	rank(sum({<Customer>}Quantity),4,1)	<= fractile({<Customer>}TOTAL aggr (rank(sum({<Customer>}Quantity),4,1),Customer), 0.70) and
	Count({<Customer>}DISTINCT Aggr(If(Only({<Customer>} PricePaid) = Min({<Customer>}TOTAL <Customer> PricePaid), Only({<Customer>} [Order Line])), Customer, [Order Line]))/Count({<Customer>}DISTINCT [Order Line])>.55
  , min({<Customer>}PricePaid))
, (Customer, (=Sum({<Customer>}Quantity), DESC)))) * Avg(1)
l_smythe80
Partner - Contributor II
Partner - Contributor II
Author

wow...

never cease to amaze me, 1 in timing and secondly pure genius as always.

thank you ever so much, i certainly owe you here.