topic Re: First Sorted Value for most frequent value in QlikView

First Sorted Value for most frequent value

LeeSmithBtn — Sat, 16 Nov 2024 19:24:21 GMT

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.

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

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

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

i hope this makes sense and the app helps.

anything would be amazing

Re: First Sorted Value for most frequent value

sunny_talwar — Mon, 23 Dec 2019 13:17:32 GMT

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

Re: First Sorted Value for most frequent value

LeeSmithBtn — Mon, 23 Dec 2019 14:37:36 GMT

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

Re: First Sorted Value for most frequent value

LeeSmithBtn — Mon, 23 Dec 2019 14:52:57 GMT

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.

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

Re: First Sorted Value for most frequent value

sunny_talwar — Mon, 23 Dec 2019 15:45:50 GMT

Don't you already get that in another column?

What is wrong with these expressions?

Re: First Sorted Value for most frequent value

LeeSmithBtn — Mon, 23 Dec 2019 16:11:26 GMT

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

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

Re: First Sorted Value for most frequent value

sunny_talwar — Mon, 23 Dec 2019 17:03:13 GMT

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

Re: First Sorted Value for most frequent value

LeeSmithBtn — Mon, 23 Dec 2019 18:02:25 GMT

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.

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

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

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

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.

Re: First Sorted Value for most frequent value

sunny_talwar — Mon, 23 Dec 2019 19:33:48 GMT

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)

Re: First Sorted Value for most frequent value

LeeSmithBtn — Tue, 24 Dec 2019 09:00:22 GMT

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

Re: First Sorted Value for most frequent value

LeeSmithBtn — Tue, 24 Dec 2019 09:32:04 GMT

although on further inspection this is actually finding the minimum paid price still and not the most frequent price paid.

just in the example app it so happens to pick the Min price which is 214.50 and is the correct value in the example, on my actual application it should be finding 260 as the most frequent paid price, but its returning 50 which is the price the customer paid the person it finds as the most frequent but with the Min price value

so looks like the part its finding is not the most frequent price correctly. but ill have a look over xmas

Re: First Sorted Value for most frequent value

sunny_talwar — Tue, 24 Dec 2019 12:04:07 GMT

Would you be able to create a sample where Min and most paid values are different?

Re: First Sorted Value for most frequent value

LeeSmithBtn — Tue, 14 Jan 2020 14:03:39 GMT

HI @sunny_talwar,

as requested, here is a copy of the app which i have changed the values to highlight the issue i am finding on my own application.

i have saved on the workings page you created, and there are 2 text objects which show the issues here:

so 1 shows £220 and the other which shows £70.

£220 for middle 55% is at greatest QTY as shown here:

£70 is then for all customers based on most frequent qty which is ignoring the middle 55% by the looks:

£220 is for the selected 30 customers (which are the middle 55% of customers) which then says the £220 has the highest QTY which is fine as an option however i just want it to retain the £220 even if any customer is selected. so its a fixed £220 no matter customer selection.

for option 2: i want to show the minimum of the frequent prices paid for the middle 55% irrespective of customers selected.

so based on the data:

option 1 would equal - £220 no matter what customer is selected as this is based on customers in the middle 55% and its the price paid most frequently by the Quantity purchased.

option 2 would equal - £185 no matter the selection as customer 27 has achieved this price most frequently of the middle 55% of customers therefore a target for all customers to be paying this price is achievable.

i hope this helps and makes sense, and is something relatively straight forward to achieve, im just all aggr'd out 🙂