How to Assign a Rank to a Date Field

Question & Answer
1

Hello,

I’d like to assign a rank to a date field, that is partitioned by customer name. The goal is to understand what item(s) customers purchase the first time they ship with us. So I’d like to identify their first purchase with an invoice date, and then see what item they bought.

I tried to write a calculated field using the rank function but am getting an error. Anyone have any good ideas? Thanks so much!

This is my initial code:
rank(InvoiceDate DESC], [customerName])

2

I think you just need to add a [ to your invoice date.

rank([{InvoiceDate} DESC],[{customerName}])

3

Thanks so much. I am still running into this error message when I set up a visualizations. I am pasting the error message at the bottom of this note.

Do you have any tips on how to set up the field wells? I haven’t been able to find a good guide on using calculated fields in visualizations. TY!

Error Message:

Table Calculation attribute reference(s) missing in field wells

|sourceErrorCode:|VISUAL_CALC_REFERENCE_MISSING|
|sourceType:|SPICE|

4

Hi @kmcen - how are you intending to use the rank after you compute it? Do you want to display it in your visual, if so, how? Or are you just trying to fliter to the first date per customer?

Using a similar formula:
rank([{order date} ASC], [{contact id}])

I can put that into a pivot table and rank each per contact (and can filter =1 too):

image
image1121×710 66 KB

But if you are trying to use it as a Dimension somewhere we will need to adjust the calc.