topic Re: Count records which are equal to firstsortedvalue? in QlikView

Count records which are equal to firstsortedvalue?

LeeSmithBtn — Sat, 16 Nov 2024 02:02:48 GMT

Good day all...

was wondering if anyone was able to help me in a complex chart expression that i do not know if it is possible but will ask the question and move on to something else if it is not possible...

so what i am trying to achieve is: finding the amount of times a specific name appears in my data set which would be equal to the first sorted value, so based on the total sales by an employee in a straight table i can find that User1 has 700 sales across 400 order lines and user 2 has 500 sales across 200 order lines. therefore user1 is returned as first sorted user with most sales, but what i want to then do is also return the number 400 as thats the amount of order lines that is within the data so that i can work out that user1 has 67% of the total order lines by dividing the 400 by the total sales for a particular customer.

does anyone know of a possible solution to this answer... i have been using the below to find the highest user by sales but cannot work out how to then show the 400.

=FirstSortedValue(SalesAgent,-aggr(sum(Total Sales),Customer,SalesAgent)) - finds User1 but now i need to find the 400 rows for this user also

any help would be fantastic..

Re: Count records which are equal to firstsortedvalue?

sunny_talwar — Mon, 04 Nov 2019 16:34:27 GMT

Is this by each Customer or across Customers? If it is across Customers, you can try this

Count(DISTINCT {<SalesAgent= {"$(=FirstSortedValue(SalesAgent,-Aggr(Sum([Total Sales]), Customer, SalesAgent)))"}>} [Order Line])

If this is by each Customer, you can try this

Count(DISTINCT Aggr(If(SalesAgent = FirstSortedValue(TOTAL <Customer> SalesAgent, -Aggr(Sum([Total Sales]), Customer, SalesAgent)), [Order Line]), Customer, SalesAgent))

Re: Count records which are equal to firstsortedvalue?

LeeSmithBtn — Mon, 04 Nov 2019 17:24:41 GMT

Hi Sunny, thank you for the amazing response time!

unfortunately this doesn't seem to have worked, its displaying numbers but they are not correct in terms of the amount of order lines which equal the user...

it brings back 376 rows which when i select the user goes on to show 770 using the first expression as i want this across all customers for each user...

maybe my explanation was difficult to understand haha apologies.

so i want to say for example: below 2 customers and 2 agents selling,

customer: total sales total order lines sales agent

customer1 . 700 400 Agent1

customer1 400 200 Agent2

customer2 350 100 Agent1

customer2 500 250 Agent2

so straight table to show:

customer: Most Sold by Agent total order lines % of Orders

customer1 . Agent1 400 67% of the 600 total orders

customer2 . Agent2 250 71.5% of the 350 total orders

unless the example provided is correct, i am not doing something correct perhaps.

any help would be amazing,

Re: Count records which are equal to firstsortedvalue?

sunny_talwar — Mon, 04 Nov 2019 17:32:26 GMT

I thought each Order Line is a single row and that is why I was doing Count(DISTINT Order Line).... but if it is aggregated, then try this

Sum(Aggr(If([Sales Agent] =FirstSortedValue(TOTAL <Customer> [Sales Agent], -Aggr(Sum([Total Sales]), Customer, [Sales Agent])), [Total Order Lines]), Customer, [Sales Agent]))

Re: Count records which are equal to firstsortedvalue?

LeeSmithBtn — Mon, 04 Nov 2019 17:48:05 GMT

Hi Sunny, yet again incredible response time blown me away...

apologies, i yet again didnt explain clearly haha 🙂 so sorry...

yes each order is on a single line, i just created an aggregated view above to show for ease so there would have been 400 individual lines which i need to count where the agent = the most sold agent... i really hope that makes sense, not used to using community yet, but will ensure i write clearer messages in future to save your expert time 🙂

so each row is a single order line with Customer and Sales Agent on each row, with the total sale amount and i need to find the most used Agent by Customer based on highest total sales and to bring back the count of lines this uses 🙂

so you will probably tell me its the first expressions you sent me

Re: Count records which are equal to firstsortedvalue?

sunny_talwar — Mon, 04 Nov 2019 18:36:59 GMT

"so you will probably tell me its the first expressions you sent me" - Yes you are right 🙂

Re: Count records which are equal to firstsortedvalue?

LeeSmithBtn — Mon, 04 Nov 2019 18:45:25 GMT

you are indeed extremely accurate on that response 🤣 i just adapted the QVW you sent back to use the previous expressions...

but got there in the end with much thanks to yourself, really great work thank you again

Re: Count records which are equal to firstsortedvalue?

LeeSmithBtn — Mon, 11 Nov 2019 17:06:34 GMT

HI @sunny_talwar i hope this message finds you well and apologies but its me again haha..

i have tried to introduce the logic as per the below to find the most used salesagent name as well as the count of times this agent has been used and the % of all the customers records that have been with this most used sales agent. but for some reason it isnt liking it as such...

-----------------------------

looking to find most used agent name by total spend

Count of times most used agent is used

% of times used vs all sales agents used

-------------------------------

customer 20 for instance shows as 134 and sales agent5 with nothing selected, but when you select customer 20 it then goes on to show 153 but still with salesagent5 which based on the mini order lines table below is accurate. just wondering why its showing as 134 (sales agent 3s total) with nothing selected.

any thoughts would be extremely welcomed 🙂

Re: Count records which are equal to firstsortedvalue?

sunny_talwar — Mon, 11 Nov 2019 17:22:24 GMT

Set analysis won't work because set analysis is evaluated once per chart.... try this instead

Sum(DISTINCT Aggr(If(SalesAgent = FirstSortedValue(TOTAL <Customer> SalesAgent, -Aggr(Sum([Total Sales]), Customer, SalesAgent)), Count(DISTINCT [Order Line])), Customer, SalesAgent))

Re: Count records which are equal to firstsortedvalue?

LeeSmithBtn — Mon, 11 Nov 2019 17:30:01 GMT

perfect as always!! seriously impressive... thank you

Re: Count records which are equal to firstsortedvalue?

LeeSmithBtn — Thu, 14 Nov 2019 16:18:35 GMT

Hi All,

im looking for some further help on the app that @sunny_talwar has helped hugely with...

i am looking to create as on the app a scatter plot showing by customer the Qty purchased and the Min price they have paid where the most amount of time by total QTY

for example Customer1 has achieved a price of 256.289 on a sum of 667 times of quantity which is the bigest by quantity. so although not the minimum price they have paid its the highest by quantity...

so i need the scatter and the table to reflect this price for the customer regardless of selection

i also want the Scatter chart to show all these values regardless of the selected customer (ideally i would like to then highlight all plots grey apart from the selected customer of which i would like to turn red to highlight selected customer on the chart)

not sure if any of this is possible, and any help would be gratefully received

Re: Count records which are equal to firstsortedvalue?

sunny_talwar — Thu, 14 Nov 2019 17:42:45 GMT

This?

Re: Count records which are equal to firstsortedvalue?

LeeSmithBtn — Thu, 14 Nov 2019 18:26:36 GMT

seriously am blown away yet again!!! thank you thats incredible

Re: Count records which are equal to firstsortedvalue?

LeeSmithBtn — Fri, 22 Nov 2019 11:10:58 GMT

HI all,

me again on this same depressing issue haha.,..

on this complicated aggr function thanks to @sunny_talwar i would like to incorporate some set analysis to look at dates also...

so i would like the logic to look for differences between the last 12 months and the previous 12 months to show the variation across the 2 12 month periods..

so the same as this but with PurchaseDate >12 months ago as 1 expression to show current 12 months:

Min({<Customer>} total <Customer>
Aggr(If(Only({<Customer>}PricePaid) = FirstSortedValue({<Customer>} TOTAL <Customer> PricePaid, -Aggr(Sum({<Customer>}[Quantity]), Customer, PricePaid)),
Only({<Customer>}PricePaid)), Customer, PricePaid))

and the same logic then showing the previous 12 months before that.

any help would be great thank you

Re: Count records which are equal to firstsortedvalue?

sunny_talwar — Fri, 22 Nov 2019 11:57:42 GMT

Would you be able to create a new thread and elaborate with an example as to what you have and what you look to get?