cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mblydt-hansen Frequent Visitor
Frequent Visitor

Coding a Custom Column Based on Relative Earliness of Date

Hi everyone,

 

I've been trying to code for a custom column in the advanced editor, but I've been unable to get it right. I'm hoping that someone can help me out!

 

My goal is to generate a "visit number" in a new column. The column would generate a numeric value for each row that is dependent upon the earliness of the visit date for each employee. For example, if employee ID "1" visits first in May 2016, then September 2016, then February 2017, the rows would have the values of 1, 2, and 3 in the new column (respectively). My dataset contains multiple distinct employee ID values, with each employee ID having multiple rows, each with unique visit dates (up to 10 separate visits). My dates are currently in the UK Format.

 

2018-06-04.pngThis is what my current dataset looks like.

 

2018-06-04 (1).pngThis is a demo of what I want my new column to look like.

 I would be grateful if someone could assist me with coding this new custom column.

 

Thanks in advance!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
SonnyChilds Established Member
Established Member

Re: Coding a Custom Column Based on Relative Earliness of Date

I used some of your data to scrap this up. Seems to work just fine. Understanding exactly what EARLIER() does helps here.

 

Visit Count = COUNTROWS(FILTER(Data, Data[ID] = EARLIER([ID]) && Data[Visit Date] <= EARLIER([Visit Date])))

 

 

image.png

2 REPLIES 2
Highlighted
SonnyChilds Established Member
Established Member

Re: Coding a Custom Column Based on Relative Earliness of Date

I used some of your data to scrap this up. Seems to work just fine. Understanding exactly what EARLIER() does helps here.

 

Visit Count = COUNTROWS(FILTER(Data, Data[ID] = EARLIER([ID]) && Data[Visit Date] <= EARLIER([Visit Date])))

 

 

image.png

mblydt-hansen Frequent Visitor
Frequent Visitor

Re: Coding a Custom Column Based on Relative Earliness of Date

Thank you very much @SonnyChilds. I'm getting exactly what I was hoping to see. I really appreciate your help!