Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

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

Sequentially number records based on Rep & Number of weeks

mmbg48
6 - 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.

5 REPLIES 5
mmbg48
6 - Meteoroid

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

Claje
14 - 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.

 

mmbg48
6 - Meteoroid

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

Claje
14 - 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.

mmbg48
6 - Meteoroid

Thanks again, worked perfectly with the advanced solution!

Labels