Help with Rank function

[QSCommUser]

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.

How to display Rank / % of the group after filtering

Hi @Liz - You can use LAC-W functions for this. Here’s an example for some common use cases. Step 1 - Create a calculated field for the YTD amount per vendor. This uses a LAC-W sumOver for partition year, and vendor. c_vendor_ytd_amt sumOver(amt,[truncDate('YYYY',dt),vendorname], PRE_FILTER) Step 2 - Create a calculated field that assigns a row number across your percentilerank dimension c_vendor_rn // use rank function as row_number over vendor name rank([dt DESC],[vendorname], PRE_FIL…

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

[Max]

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)

[QSCommUser]

@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

[David_Wong]

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?

[QSCommUser]

@David_Wong and @Max

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