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

Count records which are equal to firstsortedvalue?

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.. 

Labels (4)
3 Solutions

Accepted Solutions
sunny_talwar

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)) 

 

View solution in original post

sunny_talwar

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))

View solution in original post

sunny_talwar

14 Replies
sunny_talwar

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)) 

 

l_smythe80
Partner - Contributor II
Partner - Contributor II
Author

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, 

 

sunny_talwar

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])) 

 Capture.PNG

l_smythe80
Partner - Contributor II
Partner - Contributor II
Author

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 

sunny_talwar

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

l_smythe80
Partner - Contributor II
Partner - Contributor II
Author

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

l_smythe80
Partner - Contributor II
Partner - Contributor II
Author

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 🙂

sunny_talwar

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))
l_smythe80
Partner - Contributor II
Partner - Contributor II
Author

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