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

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

 

sunny_talwar

This?

image.png

l_smythe80
Partner - Contributor II
Partner - Contributor II
Author

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

 

l_smythe80
Partner - Contributor II
Partner - Contributor II
Author

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

 

sunny_talwar

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?