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
Solved! Go to Solution.
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
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.
Hello,
Thank you for you reply and your time to help me.
However, i'm a bit lost.
I've tried this sequence, but that gave me the same result
week 26 = 1974
week 27 = 0
i've maybe done something bad?
Stephane
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.
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..
Many thanks for your support
Stephane