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

[shivakumarmanian]

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

[zajaccount · Accepted solution]

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 · Accepted solution]

Hi @shivakumarmanian,

I think I got it:

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 · Accepted solution]

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

[shivakumarmanian]

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

[zajaccount · Accepted solution]

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

[grossal · Accepted solution]

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]

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]

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]

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