# Help with Rank function

Hi all,

I’m having some real trouble with working through some ranking on some sale analysis I have been performing. I know this has come up before and I have tried to follow along with the feedback posted in the below link, but I haven’t not been able to. I have also tried to read/work through some LAC documentation, but its I haven’t been able to make it work.

My real data set has multiple rows over various dates, but the ranking I would like to achieve is essentially ranking sales people by the amount of items sold in their respective categories. I’m looking to at doing this over a weekly and possible monthly basis.

Below is some sample data I’m working with along with my relevant calculations. My logical understanding is as follows

Calculate the total sales over the period, by item and sales person (I’m using countover as my data text based rather than integer). The data will be in a dashboard which will be filtered by item or sales person to show where they rank in each category/item or against other salespeople.

Date Sales_Person Item
23/01/2023 John Laptop
23/01/2023 John TV
23/01/2023 John Monitor
23/01/2023 Sarah Laptop
23/01/2023 Sarah Laptop
23/01/2023 Jane TV
23/01/2023 Jane Laptop
24/01/2023 Anthony Laptop
24/01/2023 Jane TV
24/01/2023 Jane Monitor
24/01/2023 Anthony Monitor
24/01/2023 Anthony Monitor
24/01/2023 Anthony TV
24/01/2023 John Laptop
24/01/2023 John TV
24/01/2023 Jane TV
24/01/2023 Jane Monitor
24/01/2023 Sarah Laptop
24/01/2023 Sarah Laptop
24/01/2023 Sarah TV
24/01/2023 Sarah Monitor
24/01/2023 Anthony Monitor
25/01/2023 Sarah Laptop
25/01/2023 Jane TV
25/01/2023 Jane Laptop
25/01/2023 Anthony Laptop
25/01/2023 Jane TV
25/01/2023 Jane Monitor
25/01/2023 Anthony Monitor
25/01/2023 Anthony Monitor

In the above, I dont understand how the ranks are greater than 4, considering there are only 4 sales people in the list.
Note: I’m my real dataset, I’m getting values in the 000’s for some of the ranks, even through I have been able to calculate the sales correctly in each category/item for each person.

sales_item_countover_ (total sales over the week, by item and sales person)

``````countOver({Sales_Person},[truncDate("WK", Date),Item, {Sales_Person}], PRE_FILTER)
``````

item_sales_countover (this is total sales in each category/item)

``````countOver({Sales_Person},[truncDate("WK", Date),Item], PRE_FILTER)
``````

item_sales_by_person_item_rank (rank the sales by item countover by sales person

``````rank([{item_sales_countover} DESC], [{Sales_Person}], PRE_FILTER)
``````

Any help with the above would be greatly appreciated. Particularly if there is some insight into how the calculation is working in the backend.

Thanks,
Q

You are partitioning your rank over sales person.

You can look further into partitions here.
https://www.sqlshack.com/database-table-partitioning-sql-server/

I think you want to partition by week.

rank([{item_sales_countover} DESC], [truncDate(“WK”, Date)], PRE_FILTER)

@Max - thanks for this. I have a read of the table partitioning.

That’s not quite what I want. The outcome (done in excel/pivot table) would look something like the below

Item Sales Person Rank
Laptop Sarah 1
Laptop John 1
Laptop Anthony 2
Laptop Jane 3
Monitor Anthony 1
Monitor Jane 2
Monitor Sarah 2
Monitor John 2
TV Jane 1
TV John 1
TV Sarah 2
TV Anthony 3

By date i get the below in QS:

Thanks

If the date isn’t relevant to the calculation of the ranking, you shouldn’t include it in your partition.

item_sales_countover = countOver({Sales_Person}, [Item, {Sales_Person}], PRE_FILTER)
ranking = rank([{Sales per Item} DESC], [Item], PRE_FILTER)

Does this result look right?

Thanks again for your help and feedback here. I have now made this work as I’m expecting.

David - what you provided above is exactly what I was looking for, however it didnt work when removing the data.

However, I did play around with using the date as partition (this was needed in my actual data set, as here is a date filter on the dashboard being created) and I have been able to get this work as expected using denseRank function.

What I needed in the end was to denseRank([{salesperson}] DESC, [trunc(“WK”, date), Salesperson, Item], PRE_FILTER).