We’ve extended Inspire Early Bird Pricing until March 1. Register now and enjoy 20% off conference passes and 10% off training passes. P.S. Don’t forget to bring friends! When you sign up for five or more tickets, you get an extra 20% discount on conference passes. Learn more now.
alteryx Community

# Alteryx Designer Desktop Discussions

SOLVED

## Generate rows using weeknumber?

7 - Meteor

Hello,

I use an internal weeknumber and i would want calculate it using the calendar week number.

For example, calendar week for the week 26 = internal week number 1974, then week 27 should be 1975, etc

i would want to generate it automatically.

I've alreay create a job to get the calendar weeknumber, but i don't find how to proceed then.

in attachment my job, all advice are welcome :-)

thank you

Stephane

7 REPLIES 7
9 - Comet

Hi @stefanem,

The Multi-Row formula tool should solve this problem as it will enable you to compare the the current row for 'Current Week' with the row before and update the 'Internal Week' field as appropriate. Your expression will look like:

IF [Row-1:Current Week] = [Current Week]
THEN [Internal Week]
ELSE [Internal Week] +1
ENDIF

Or you could make it even more bulletproof with:

IF [Row-1:Current Week] = [Current Week]
THEN [Internal Week]
ELSE [Internal Week] + ([Current Week]-[Row-1:Current Week])
ENDIF

Does this solve your problem? Experiment with that tool as I'm fairly sure it will be of help for you here.

Regards,

Luke

Alteryx Certified Partner for Keyrus UK

9 - Comet

P.S. This relies on you already having the first one in there (as you showed in your screenshot) - if you don't and you want to generate it from scratch then you just have to build that into the formula similar to this:

IF IsNull([Row-1:Current Week]) <---This is a test for if it is the first field (Could also be IF [Row-1:Current Week] = 26)

THEN 1974

ELSEIF [Row-1:Current Week] = [Current Week]
THEN [Internal Week]
ELSE [Internal Week] + ([Current Week]-[Row-1:Current Week])
ENDIF

Forgot to add that the tool should be set up to 'Update Existing Field' as Internal Week.

Let me know how you get on.

7 - Meteor

Hello,

However, i'm a bit lost.

I've tried this sequence, but that gave me the same result

week 26 = 1974

week 27 = 0

Stephane

9 - Comet

Sorry @stefanem I made a bit of a mistake. The formula should read:

IF IsNull([Row-1:Current Week])

THEN 1974

ELSEIF [Row-1:Current Week] = [Current Week]
THEN [Row-1:Internal Week]
ELSE [Row-1:Internal Week] + 1
ENDIF

Please see attached solution so you can see it working.

7 - Meteor

hi back

i've fixed my error, but the result is not as expected. in attachment my configuration

Stephane

7 - Meteor

Hi,

i've tried your sequence, and the result is in attachment.

i've maybe done  a mistake with the group by in multirow-formula

7 - Meteor

Hi again,

i've fixed my error, it was due to the fact i created a new field instead of update the existing.

that works perfectly now..