Generate rows using weeknumber?

[stefanem]

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

[lminors]

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

[lminors]

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.

[stefanem]

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

[lminors · Accepted solution]

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.

[stefanem]

hi back

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

Stephane

[stefanem]

Hi,

i've seen your last reply,  but your version seems to be different than mine,i cannot open your file.

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

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

[stefanem]

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