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

Multirow Date Formula

San_N
6 - Meteoroid

Hey Guys,

 

I am trying to write a formula to add dates for example (see the picture below) - 

I have all rows till Date and I am trying to get the new date column, (Basically adding 7 days if there is a null value in cht_itemcode)

 

I have been using this formula but it doesn't seem to work -

IF IsNull([cht_itemcode]) THEN (DateTimeAdd([Row-1:Date],7,"Days")) ELSE [Row-1:Date] ENDIF

 

Capture.JPG  

Please let me know if there is a solution.

 

Thank you

9 REPLIES 9
DavidP
17 - Castor
17 - Castor

You need to convert both date and newdate to the standard datetime format with datetime parse tools. The standard date format is '2018-11-18'

San_N
6 - Meteoroid

Yup, I have it in the same format in the alteryx tool.

 

Capture.JPG

DavidP
17 - Castor
17 - Castor

You can also use a formula tool to change the date format and then change the datatype with a select tool like this

 

datetimeconvert.png

DavidP
17 - Castor
17 - Castor

Ok, have a look at attached workflow.  Formula is the same as yours. I guess new date is the field created by your multi-row formula. Did you set the type as date?

 

datetimeconvert.png

San_N
6 - Meteoroid

It still doesn't work.

 

Can you give me a formula to get new date column from Date column. that works.

The formatting is right.

San_N
6 - Meteoroid

But the first cell is null as well.

San_N
6 - Meteoroid

Also the date field has all same dates.

Basically I want to keep adding as soon a line break appears and continue that new value.

 

Please refer my attached picture.

 

Thank you.

DavidP
17 - Castor
17 - Castor

ok, I think this now does what you want.

 

The formula is

 

if !isnull([Row-1:new date]) then
if isnull([cht_itemcode]) then
datetimeadd([Row-1:new date],7,'days')
else [Row-1:new date]
endif
else [Date]
endif

 

new date.png

San_N
6 - Meteoroid

Thank you, this works.

Labels