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.

Generate list of dates

swapsingh2712
8 - Asteroid

Hello All,

 

I'm trying to create an alteryx workflow which will generate values based on the dates mentioned in the existing column.

 

So I've few tests that has a activation and decommission date.

 

Now I've to add a column using alteryx which will generate the current dates.

 

so here are the conditions on which the values should be generated

 

Condition 1: If test 1 activation date 10/1/2019 and no decommission date. It should generate 6 months previous and future  months like this (based on the current month like 11/24/2021)

5/1/2021
6/1/2021
7/1/2021
8/1/2021
9/1/2021
10/1/2021
11/1/2021
12/1/2021
1/1/2022
2/1/2022
3/1/2022
4/1/2022

 

 


Condition 2: 
If the activation date is 9/1/2021 and no decommission date. the new column should generate 6 months forward dates based on the current month and activation date 

 

9/1/2021
10/1/2021
11/1/2021
12/1/2021
1/1/2022
2/1/2022
3/1/2022
4/1/2022

 

Condition 3: If the activation date is 10/1/2019 and decommission date is 12/1/2021. The dates should be like this

 

5/1/2021
6/1/2021
7/1/2021
8/1/2021
9/1/2021
10/1/2021
11/1/2021
12/1/2021

It should be 6 months previous to the activation date month and end till the decommission date.

 

All these data will be generated based on months and year. I've attached an excel sheet with expected input and output.

 

Also in the output I've highlighted the section that should not show up in the output.

 

Dataset is also available in the attachment

 

Please let me know if we can achieve this using alteryx.

 

the current month will be generated using alteryx.

 

Regards,

Swapsingh2712

4 REPLIES 4
csmith11
11 - Bolide

Please see solution attached:

 

csmith11_0-1638914597362.png

 

 

Pay close attention to the generate rows tool.

 

csmith11_1-1638914614598.png

 

Example Output

csmith11_2-1638914624041.png

 

As a note the output example you provided takes the current date + 5 months: For Test 1 this yields 12 months.

IF you wanted current month plus 6 month the final result would actually be 13 months for Test 1. 

 

 

swapsingh2712
8 - Asteroid

Hello @csmith11 

 

Thank you so much for your response.So I'm getting the date conversion error while running the workflow

 

Screenshot attached.

 

Could you please advise

 

Regards,

swapsingh2712

swapsingh2712
8 - Asteroid

@csmith11 

 

also one thing I've noticed that whenever I'm trying to use the attached excel dataset, value for activation date and decommission date is showing null in formula tool.(although activation date has date values)

 

dataset and workflow attached

Jonathan-Sherman
15 - Aurora
15 - Aurora

Hi @swapsingh2712,

 

You were using a DateTimeParse() rather than a DateTimeFormat() to change the format of the dates:

 

JonathanSherman_0-1638964284875.png

 

I've attached an updated workflow for you to download if needed!

 

Kind regards,

Jonathan

 

Labels