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

First Sorted Value for most frequent value

Hi All,

looking for some support again, thanks so far to @sunny_talwar for his extraordinary help on the app.

i have some columns in the app which find the min price paid across all customers by sales agent where they are within the rank 15% and 70% and then the price across all customers as the min target price that should be achieved. 

i also have a column which finds the most frequent price paid by the total QTY at a customer level (min price at greatest QTY):

 

so this is the min price paid and no matter if a customer is selected it retains the 214.5 as the min price paid.

Screenshot 2019-12-23 at 10.08.25.png

 

what i then have is a column that finds the most frequent price paid by these customers based on their QTY

Screenshot 2019-12-23 at 10.08.31.png

as you can see the customer 12 at the top as a frequent price of 192.50 so this is the correct min frequent price. however i want the last column to display the frequent price of the customer using the same logic as the min price where the qty rank > 15% and less than 70%. so using the same Min customer price logic but incorporating the Frequent price instead of the Min price and dropping the part that says >55% as this would not be necessary at this stage as the frequent price based on QTY is in the middle qty ranks.

so use this logic principal but without the >.55 line 4

Screenshot 2019-12-23 at 10.15.23.png

and incorporate this into the measure so that it finds the frequent price across all customers. and retain ti no matter on selection of customer

Screenshot 2019-12-23 at 10.15.35.png

i hope this makes sense and the app helps.

anything would be amazing

 

1 Solution

Accepted Solutions
sunny_talwar

Are you after this?

=FirstSortedValue({<Customer>} TOTAL PricePaid, -Aggr(Sum({<Customer>}Aggr(If(Only({<Customer>} Aggr(NODISTINCT Rank(Sum({<Customer>} Quantity), 4, 1), Customer)) > Fractile({<Customer>}TOTAL Aggr(Rank(Sum({<Customer>}Quantity), 4, 1), Customer), 0.15) and 
																			  Only({<Customer>} Aggr(NODISTINCT Rank(Sum({<Customer>} Quantity), 4, 1), Customer)) <= Fractile({<Customer>} TOTAL Aggr(Rank(Sum({<Customer>}Quantity), 4, 1), Customer), 0.70) and
																			  Only({<Customer>} PricePaid) = Min({<Customer>} TOTAL <Customer> PricePaid), Only({<Customer>}Aggr(NODISTINCT Sum({<Customer>}Quantity), Customer)), 0), PricePaid, Customer)), PricePaid))
																			  
*

Avg(1)

View solution in original post

12 Replies
sunny_talwar

What is the expected output you are hoping to see here?

l_smythe80
Partner - Contributor II
Partner - Contributor II
Author

Hi Sunny,

the expected output is the same as the column that shows 214.5 no matter what customer is selected and is found using the logic previously. 

 

so in short the middle 55% flag column shows all the customers where they meet the criteria being in the middle 55%, and of these customers the min price at greatest qty is then 214.50 by customer 22, so all customers should be comparing to the most frequent price of 214.50 no matter what customer is selected

i hope that makes sense

 

 

l_smythe80
Partner - Contributor II
Partner - Contributor II
Author

so looking at the below screen shot...

the requirement is based on the Min Price at Greatest QTY column, where the Middle 55% flag is a 1 to find the customer with the minimum frequent price. so based on the screenshot customer26 through to customer11 are within the middle 55% based on QTY rank.

Screenshot 2019-12-23 at 14.45.39.png

and the min price of these customers is 214.50, so i want no matter customer is selected that this shows 214.50 in the column showing 192.50 Min price total at greatest QTY or within a text object to be able to work out potential variances between their on frequent price and the minimum frequent price someone else has achieved,. currently it recalculates based on selecting customers

 

sunny_talwar

Don't you already get that in another column?

image.png

What is wrong with these expressions?

l_smythe80
Partner - Contributor II
Partner - Contributor II
Author

it does in that example, but that is the min price paid where achieved greater than 55% of the time and not the most frequent price paid.

so on this example it works correctly by chance, but in the main app (not able to share screenshots of live customer app), it doesn't work out like this and that the min price has been achieved over 55% where as actually its not the most frequent price paid.

i am trying to combine the 2 logics into 1 ideally, i'm struggling to write the question that makes sense and i apologise for that...

what i am trying to do is take away the 55% logic as this means there are sometimes customers who dont ever have 55% at the min price and therefore the variance and savings are never displayed. 

so to get around this, the thought is by finding the most frequent price paid by a customer takes the need  to have >55% logic in there.

 

so still taking the same middle 55% of customers, then finding the price that each customer pays most frequently and then take the min of them to help identify a guide price, without requiring the need to have a set % of greater than 55%

 

for example purposes only...

lets say we only have 7 customers for product A, and none of them ever achieve  the min price greater than 55% of the time, then we wouldnt ever have a target price, where as i would be able to find a most frequent min price. which in this example would be 237. across all the customers for ProductA

Screenshot 2019-12-23 at 16.09.49.png

 

does that make sense? im struggling to write to make sense sorry

 

 

 

sunny_talwar

Unfortunately, I am not sure I am able to understand what we are trying to do here...

l_smythe80
Partner - Contributor II
Partner - Contributor II
Author

thats Ok sunny its kind of hard to explain the issue... 

so what i want to do is find the most frequent price paid instead of the min price paid.

so currently this logic below (screenshot1) finds the price paid by the customer where based on QTY is the most often. screenshot 2 and 3: shows these in practice.

instead of that, i want to say find the price they paid using this logic below

screenshot1: finds the frequent price by customers. so the most common price they paid based on the QTY purchased and not the Minimum price. this is correct based on the next 2 screen shots.

Screenshot 2019-12-23 at 10.15.35.png

 

screenshot2:  customer 21 has a Min price achieved of 264 based on 28.38% of the time this price was achieved.

Screenshot 2019-12-23 at 17.34.57.png

screenshot3: customer 21 has achieved a price of 269.5 the most based sum of QTY.

Screenshot 2019-12-23 at 17.35.03.png

so find the price that each customer has paid most often by the total Qty which the above logic does...

 

but i only want to find the Frequent price where they fall in the middle 55% of customers based on QTY like this

Screenshot 2019-12-23 at 10.15.23.png

so the line that says >.55 in the if statement i want to remove as this effectively means that if no one achieves the min price >55% of the time then there would never be a min price. where as if i move to the min most common price achieved by customers then there would always be a value. 

so ideally i want to say if you are a customer who fits in the middle55% then find the most frequent price that the customer has paid... instead of finding the Minimum price. because if no customer ever buys at the minumum price greater than 55% of the time, then there is never a saving. 

where as if i find the most frequent price by all customers by QTY as displayed by customer 21 in this case 269.5 (most purchased price)  and not 264(min price)  and they were the lowest price customer in the middle 55% range (based on column middle55% logic) . then the target price for all customers would be 269.5.

does that help or made things even worse to follow, sorry haha...

what im trying to do is iron out where no customer has ever paid the min price >55% of the time and move to a logic where there would always be a Min price achieved where the customer has achieved it there most amount of time.

 

 

 

 

sunny_talwar

Are you after this?

=FirstSortedValue({<Customer>} TOTAL PricePaid, -Aggr(Sum({<Customer>}Aggr(If(Only({<Customer>} Aggr(NODISTINCT Rank(Sum({<Customer>} Quantity), 4, 1), Customer)) > Fractile({<Customer>}TOTAL Aggr(Rank(Sum({<Customer>}Quantity), 4, 1), Customer), 0.15) and 
																			  Only({<Customer>} Aggr(NODISTINCT Rank(Sum({<Customer>} Quantity), 4, 1), Customer)) <= Fractile({<Customer>} TOTAL Aggr(Rank(Sum({<Customer>}Quantity), 4, 1), Customer), 0.70) and
																			  Only({<Customer>} PricePaid) = Min({<Customer>} TOTAL <Customer> PricePaid), Only({<Customer>}Aggr(NODISTINCT Sum({<Customer>}Quantity), Customer)), 0), PricePaid, Customer)), PricePaid))
																			  
*

Avg(1)
l_smythe80
Partner - Contributor II
Partner - Contributor II
Author

absolute superstar as always and certainly the best christmas present im getting this year...

thank you for everything on this so far to date. most appreciated