Basic rank function to apply to the primary key partitioned by one dimension

[Massi]

Hi,

I am going crazy over a basic rank function.

I have a table with a log, and I’d like to get the rank for each log id, partitioned by their session id.

The table has these tables:

log_id	session_id	user_id	created_time
133456941	6841019	110421	18/08/2025 23:59:37
133456943	6841019	110421	18/08/2025 23:59:37
133456929	6841021	110411	18/08/2025 23:59:59
133456930	6841021	110411	18/08/2025 23:59:59
133456931	6841021	110411	18/08/2025 23:59:59
133456932	6841021	91713	19/08/2025 00:00:00
133456933	6841021	91713	19/08/2025 00:00:00
133456934	6841021	91713	19/08/2025 00:00:00
133456935	6841021	91713	19/08/2025 00:00:00
133456936	6841021	91713	19/08/2025 00:00:00
133456937	6841021	91713	19/08/2025 00:00:00
133456938	6841022	29280	19/08/2025 00:00:02
133456939	6841022	29280	19/08/2025 00:00:02
133456940	6841022	29280	19/08/2025 00:00:02
133456942	6841022	29280	19/08/2025 00:00:02
133456944	6841022	29280	19/08/2025 00:00:02
133456945	6841022	29280	19/08/2025 00:00:02

The would be ordered by log_id, which is the unique primary key, but to make it more readable I sorted them by the session_id.

What I want to achieve is simply:

log_id	session_id	user_id	created_time	Rank
133456941	6841019	110421	18/08/2025 23:59:37	1
133456943	6841019	110421	18/08/2025 23:59:37	2
133456929	6841021	110411	18/08/2025 23:59:59	1
133456930	6841021	110411	18/08/2025 23:59:59	2
133456931	6841021	110411	18/08/2025 23:59:59	3
133456932	6841021	91713	19/08/2025 00:00:00	4
133456933	6841021	91713	19/08/2025 00:00:00	5
133456934	6841021	91713	19/08/2025 00:00:00	6
133456935	6841021	91713	19/08/2025 00:00:00	7
133456936	6841021	91713	19/08/2025 00:00:00	8
133456937	6841021	91713	19/08/2025 00:00:00	9
133456938	6841022	29280	19/08/2025 00:00:02	1
133456939	6841022	29280	19/08/2025 00:00:02	2
133456940	6841022	29280	19/08/2025 00:00:02	3
133456942	6841022	29280	19/08/2025 00:00:02	4
133456944	6841022	29280	19/08/2025 00:00:02	5
133456945	6841022	29280	19/08/2025 00:00:02	6

I though that I could get this with a simple:

denseRank([{log_id}] ASC, [{session_id}]

But this is not working.

How to do that? All the examples in the docs are with aggregated data (sum, max…), and i cannot find a way to do something so simple, in SQL it’d take five minutes but I would prefer to keep my query as simple and fast as possible.

Thanks.

Ciao,
Massimo.

[Brett]

Hi @Massi ,

What’s not working about your calculated field, have you tried switching to DESC instead of ASC?

To achieve a layout like what you’re trying to achieve, you can sort your visual off multiple fields which, in your case will allow you to sort by session id and then rank.

I built a simple example in Arena view for you to review:
Basic rank function to apply to the primary key partitioned by one dimension

Let me know if you have any additional questions or if this helps point you in the right direction.

[Massi]

Hello @Brett,

First of all thank you for your help.

Yes, I tried sorting it in descending order but that did not work either. I did some more tests and attempts but none was successful.

I know that I can sort by multiple fields but I actually need to add the rank to use them for other calculations.

Thank you.

(I realize that my formula is missing the closing parenthesis but it’s just a copy and paste mistake, the original has it.)

[Brett]

Hi @Massi,

So when you create your denseRank function, you said it was not working. Is it not allowing you to create the calculated field or is it producing incorrect results?

Another option that could make this easier for me to assist further; if you’re able to upload a sample copy of your analysis with anonymized data to Quick Sight Arena, I can test out a few different options.

QuickSight Arena -- An embedded instance of QuickSight within the Community

[Arena Graphic 2] Watch this video for a 30 second intro to Arena. Quick Sight Arena provides you with an embedded instance of Quick Sight right within the Community. Using Arena, you can: Explore creating Quick Sight data visualization dashboards for free Publish dashboards to the Community Viz Gallery to showcase your skills Include dashboards depicting your use case while posting questions in the Community Q&A Clone underlying analysis views of published dashboards in order to lea…

[Massi]

Hi @Brett !

Quick Sight returns syntax error, so it’s not very descriptive on what the issue is.

The data above is taken from my table, anonymizing the IDs. There are other fields but they are all dimensions except one which is a measure, the amount field, which is a number, each session_id has an amount, so it’s repeated for all entries for that session_id, but for the rest that is exactly the data and as you see it’s pretty simple.

Thanks!

Ciao,
Massimo.

[Brett]

Hi @Massi,

Following up here as it’s been awhile since last communication took place on this thread. Are you still encountering the same issue(s)? I included a link above to my example of testing out this scenario; as my dataset is different, I had to use different field names but the following calculated field worked for me in this scenario:
denseRank([{Order ID} DESC], [{Customer ID}])

If you’re still encountering issues while trying to achieve, could you please share a screenshot of your calculated field syntax while hovering over it to show the error that you’re receiving?

If you’d like to check out my example above, make sure to hit the ‘copy analysis’ button on the left side after opening; this will allow you to see the analysis view of how I set everything up.

If we do not hear back within the next 3 business days, I’ll close out this topic.

Thank you!

[Brett]

Hi @Massi,

Since we have not heard back, I’ll go ahead and close out this topic. However, if you have any additional questions, feel free to create a new post in the community.

Thank you!