community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Sequentially number records based on Rep & Number of weeks

Meteoroid

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.

Meteoroid

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

Magnetar
Magnetar

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.

CommunityMultiRowGroupBy.PNG

 



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.

 

Meteoroid

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

Magnetar
Magnetar

No problem!

I was editing in extra stuff after I posted (like the screenshot above) so I'm not surprised that it might have been missed.

Meteoroid

Thanks again, worked perfectly with the advanced solution!

Labels