Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

How to select last 5 days' records from each calendar month of given data set?

shivakumarmanian
6 - Meteoroid

Hi Team,

 

I have a bunch of sales records containing the sales dates.  I need to select all the records of last 5 days of each calendar month.  I would like to have the report saved in a file with Customer Name, Sale Date (only those records that are sold during the last 5 days of each calendar month).

 

Kindly help,

Thanks,

Siva

12 REPLIES 12
zajaccount
9 - Comet

Hi,

 

I think this could be done by using the following formula: 

 

if datetimediff(DateTimeTrim([DateTime_Out],"lastofmonth"),[DateTime_Out],"days")<=5 then
1 else 0 endif

 

then, filter by "True" in the newly established column.

 

Please find a sample workflow attached.

grossal
15 - Aurora
15 - Aurora

Hi @shivakumarmanian,

 

I think I got it:

 

grossal_0-1588501089589.png

 

What happens:

- Converting Date to an actual date using this formula:

 

DateTimeParse([Sale Date],"%d-%b-%Y")

 

- Getting the fifth last day of the month for the date

 

DateTimeAdd(DateTimeTrim([Date],'lastofmonth'),-5,'days')

 

- Filtering everything

- Remove our helping columns

 

Workflow attached. Let me know what you think 😃

 

Best

Alex 

shivakumarmanian
6 - Meteoroid

Hi grossal, Awesome, thank you so much for your quick note.  It works!  Thank you - Siva

shivakumarmanian
6 - Meteoroid

Hi @ zajaccount, Many thank for your help.  This is great and it works for me.  Thanks again - Regards, Siva

zajaccount
9 - Comet

Glad I could help. If you don;t mind, please accept the solution to the topic 😉

grossal
15 - Aurora
15 - Aurora

That's great! 

 

It would be good if you accept both posts as a solution (I noticed @zajaccount was also able to help). This helps others to find the solutions they need more quickly 😃

shivakumarmanian
6 - Meteoroid

Hi zajaccount, i have now accepted the solution.  I am new and hence didn't realise that i should accept the solution to get others benefitted.

 

Regards,

Siva

shivakumarmanian
6 - Meteoroid

Hi grossal, I am new to the discussion forum and hence didn't realise that i should be accepting the solution.  Many thanks again for your help.  I have now accepted the solution.  Regards, Siva

TMHC
5 - Atom

Hi- how would I do this if I was trying to get the first 2 days records from the calendar month? I tried this but it didnt work:

 

 

if datetimediff(DateTimeTrim([ApprovedDate],"Firstofmonth"),[ApprovedDate],"days")>=2 then
1 else 0 endif

Labels