Multiple Date Columns from one date column till specific years.

[kiotsuresh]

Hi,

I have A requirement like below.

Input:

Stock	Buy/Sell	Stock Date	Start Date	End Date	Price
Amazon	Buy	12/31/2023	11/29/2023	3/1/2024	10
Apple	Buy	12/31/2023	12/27/2023	5/1/2024	20
GE	Buy	12/31/2023	12/27/2023	5/1/2024	30
Microsoft	Buy	12/31/2023	11/24/2023	5/1/2024	40
Applied Materials	Buy	12/31/2023	11/24/2023	5/1/2024	50

Output:

Date Column output : The Column after Price needs to be derived from Stock Date and the other columns needs to be created for upcoming months for next 4 years.

Value output:  the values for the new date columns needs to be created by comparing the column header date with start date and end date and price

"if start date<column header date and end date >column header date then Price else 0 end"

. Is this possible to achieve in Alteryx

Stock

Buy/Sell

Stock Date

Start Date

End Date

Price

12/31/2023

1/31/2024

2/29/2024

3/31/2024

4/30/2024

5/31/2024

6/30/2024

Amazon

Buy

12/31/2023

11/29/2023

3/1/2024

10

Apple

Buy

12/31/2023

12/27/2023

5/1/2024

20

GE

Buy

12/31/2023

12/27/2023

5/1/2024

30

Microsoft

Buy

12/31/2023

11/24/2023

5/1/2024

40

Applied Materials

Buy

12/31/2023

11/24/2023

5/1/2024

50

[aatalai]

@kiotsuresh this should help, let me know how you get on

[kiotsuresh]

Will this work if the incoming fields are in Date Data Type?

[aatalai]

should do, but if not use a select tool/formula tool to change data types

[kiotsuresh]

if you run your workflow for Dec 2023&Jan 2024 we see the correct days, 2023_12_31&2024_01_31 for rest of the months the days are populated as 29 and 28 which is incorrect, which should be the total number of days in the month. Also Is it feasible to have the new columns like 31-Dec-2023 , 31-Jan-2024 instead of 2023_12_31,2024_01_31, if we can achieve this that will be the solution. I can do this using a select tool but i need it to be dynamic. please help

[danilang · Accepted solution]

Hi @kiotsuresh 

Here's a dynamic version.  

It uses @aatalai's basic framework, but then uses a RecordId to as the Column Header field in the Crosstab to get the proper column order and then a Dynamic Rename to get to correct date format in the headers

Dan 

 

[kiotsuresh]

Thanks Dan, Wrt the no of days in every month, your workflow also produces an output where except Dec 2023&Jan 2024 rest all the month days is incorrect for ex: 29-Feb-2024 is right where 29-Mar-2024 is incorrect ( it should be 31-Mar-2024), Is there a way to fix this?

[danilang]

Hi @kiotsuresh 

This is related to a logic error in the Generate rows tool.  The original increment formula was

DateTimeAdd([Stock Date],1,"month")

This add 1 month to the date by incrementing the month number and then adjusting for shorter months.  So 2024-01-31 + one month is 2024-02-31, but then adjusted to 2024-02-29 since Feb only had 29 days.  Adding one month to 2024-02-29 gives 2024-03-29 which is valid, so the day never increases beyond 29 in any subsequent month

To get around this, change the Loop Expression to 

ToDate(DatetimeTrim(DateTimeAdd([Stock Date],1,"month"),"lastofmonth"))

This pushes the new date to last day of the current month and then converts to a date

Dan

[binuacs · Accepted solution]

@kiotsuresh one way of doing this