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
Date | Value | Desired Value |
1/1/20 | 2 | 9 |
1/2/20 | 3 | 9 |
1/3/20 | 4 | 9 |
1/31/20 | 9 | 9 |
2/1/20 | 5 | 7 |
2/2/20 | 6 | 7 |
2/27/20 | 8 | 7 |
2/28/20 | 7 | 7 |
3/1/20 | 8 | 3 |
3/2/20 | 4 | 3 |
3/9/20 | 6 | 3 |
3/10/20 | 3 | 3 |
Solved! Go to Solution.
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.
Check out the attached workflow to see this in action and let me know if you have any questions.
@CharlieS Thank you!