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

Find Last Day of Each Month and Apply Value

anovohatski1
7 - Meteor

Good afternoon,

 

Hoping that someone could help me here. I am trying to find the last available date within each month of data, then take value and apply it to all of the dates within each month. 

 

For instance, if I have January 31st value is 9 then i would apply it to all days within January (see Desired Value column). However, if in March the latest day that I have is March 10 - then I would use the value from that day and apply it all prior days within March. Hope it makes sense.

 

Is this even possible in Alteryx?

 

Dataset

DateValueDesired Value
1/1/2029
1/2/2039
1/3/2049
1/31/2099
2/1/2057
2/2/2067
2/27/2087
2/28/2077
3/1/2083
3/2/2043
3/9/2063
3/10/2033
2 REPLIES 2
CharlieS
17 - Castor
17 - Castor

Hi @anovohatski1 

 

This is how I would go about doing this:

- Convert the "1/1/20" values into the standard date format of "2020-01-01" so DateTime functions in Alteryx can be used and sorting is ordered chronologically. 

- Use these date values to identify the month that each record falls within.

- Sort the records by date and use a Sample tool to find the last record available for each month.

- Join that value to the original records using the month field.

 

20200826-LastofMonth.PNG

 

Check out the attached workflow to see this in action and let me know if you have any questions. 

anovohatski1
7 - Meteor

@CharlieS Thank you! 

Labels