Sequentially number records based on Rep & Number of weeks

I am trying to number from 0-x for each sales rep that have different number of records based on week ending date. For instance rep 1 started 1-1-2016 and my data goes to 12/31/2018 so they would have roughly 156 weeks(records). Rep 2 started in 1-1-2018 and again data goes to 12/31/2018 so they would have 52 weeks. So I want to number each week ending date accordingly. Thanks for any help in advance. I have attached some sample data with the column week number being what I want to sequentially number.

Cohort Test.xlsx

Common Use Cases

Accepted answers

Claje

Hi,

Try the Multi-Row Formula tool with the following code:

(([Row-1:Week Number])+1)

And use the "Group By" Settings inside the tool to Group By your "Consolidated Rep" field.

You will then need a formula tool afterwards to get these numbers to start at 0 for [Week Number]

[Week Number] -1

EDIT:

Alternatively, if you set the "Values for Rows that don't exist" to "NULL", you can use this Multi-Row Formula

IF ISNULL([Row-1:WeekNumber]) THEN
0
ELSE
[Row-1:WeekNumber]+1
ENDIF

This is a slightly more complex formula, but saves you a tool.

All comments

mmbg48

I tried using that earlier but it was numbering the whole file not by rep.

Claje

Hi,

Try the Multi-Row Formula tool with the following code:

(([Row-1:Week Number])+1)

And use the "Group By" Settings inside the tool to Group By your "Consolidated Rep" field.

You will then need a formula tool afterwards to get these numbers to start at 0 for [Week Number]

[Week Number] -1

EDIT:

Alternatively, if you set the "Values for Rows that don't exist" to "NULL", you can use this Multi-Row Formula

IF ISNULL([Row-1:WeekNumber]) THEN
0
ELSE
[Row-1:WeekNumber]+1
ENDIF

This is a slightly more complex formula, but saves you a tool.

mmbg48

Thank you for that I do not think I was grouping by rep sorry I missed that in your reply through my email.

Quick Links

This months top contributors

atcodedog05 19598

Qiu 15879

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703