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

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

13 REPLIES 13
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