Hi,
In the file attached I want to workout with something in which, the data remains as it is just the Posting Date and Document Date changes every month.
For eg- For May its showing
5/31/2022 |
So when I'll run the data for June it changes to "Last date of June" i.e 6/30/2022.
Thanks in Advance
Solved! Go to Solution.
@Tid14 I can't download your sample yet as it's still doing a virus scan. However, if you want this to always just be dynamic to the end of the current month (when you run the flow), you can just use the 'lastofmonth' DateTimeTrim(), as so:
DateTimeTrim(DateTimeNow(),'lastofmonth')
If you're precious about the format being mm/dd/yyyy then you'll have to wrap it in the DateTimeFormat() function and change the output data type to a string like so:
DateTimeFormat(DateTimeTrim(DateTimeNow(),'lastofmonth'),'%m/%d/%Y')
Hey @Tid14,
You can use some of the date time functions to achieve this:
DateTimeTrim(DateTimeNow(),"lastofmonth")
This will always return the end of the month whenever you run it.
Any questions or issues please ask :)
HTH!
Ira
Hi @Tid14
You could also just use the 'DateTimeLastOfMonth()' function to do the same thing as the others suggested.
https://help.alteryx.com/20221/designer/datetime-functions
@Luke_C I've been on that page so many times and must've skimmed that - never noticed it as a standalone function! Cheers!
Thanks @Luke_C 😅
If its coming virus scan, I can paste the data for your reference. I want Dates to be in the column next to Document date and posting date. Do i need to use a Cross Tab first and then formula or is there any other way too?
Thanks!
COMPANY | XYZ |
DOCUMENT DATE | 5/31/2022 |
POSTING DATE | 5/31/2022 |
DOCUMENT TYPE | PP |
DOCUMENT REFERENCE | 1943 |
DOCUMENT TEXT | Reclass PQQ loss |
CURRENCY | XXXX |
EXCHANGE RATE | |
LEDGER GROUP | |
SPECIAL PERIOD | |
MJE TOTAL | 0.00 |
No need, you can just do a check as part of your Formula expression to see if it's one of those rows:
if [COMPANY] in ('DOCUMENT DATE','POSTING DATE') THEN DateTimeFormat(DateTimeLastOfMonth(),'%m/%d/%Y') else [XYZ] endif
If you're fussy about the leading 0 then you can add the TrimLeft() function too:
if [COMPANY] in ('DOCUMENT DATE','POSTING DATE') THEN Trimleft(DateTimeFormat(DateTimeLastOfMonth(),'%m/%d/%Y'),'0') else [XYZ] endif
That was really helpful
Thanks Cheers 👍
Thanks Cheers :)