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

End-of-Month, End-of-Week, Week Number, End-of-Quarter | Boolean from Booking Date

maier_alexander_MO
8 - Asteroid

Dear all,

 

I have currently built a first workflow which aggregates various data sources into one large table that basically shows different accounts for each day with their respective amounts for that booking day.

 

In the next step I want to create a dashboard in Tableau, but for that would urgently require the information whether the date of this line item is the End-of-Month, End-of-Week and which Calendar Week and whether the date is the End-of-Quarter.

 

This is required since the data can only be reported for a single day since summing account balances makes no sense (compared to like for example cashflows). Thus in the end, I could simply add the column EOW in Tableau as a filter and easily show the relevant amounts.

 

The down below shows how the 4 additional rows should look like:

 

Booking DateEOMEOWCWEOQ
31/08/2019TrueFalse35False
13/09/2019FalseFalse37False
15/09/2019FalseTrue37False
30/09/2019TrueFalse40True

 

Do any of you have had the same challenge and already found formulas for this? The formulas should always check for the actual last day of a month/week/quarter (not the last working day) since my formulas update weekend days with the amounts from Friday anyways.

 

Thank you very much in advance for your help on this!

Kind regards,

 

Alex

3 REPLIES 3
jdunkerley79
ACE Emeritus
ACE Emeritus

 I would suggest:

 

EoM: %b gives the month

 

DateTimeFormat([Date],"%b") != DateTimeFormat( DateTimeAdd([Date],1,"days"),"%b")

 

 

 

EoW (based on the sample you gave Monday is the first day of the week): %a - gives the day of the week

 

DateTimeFormat(DateTimeAdd([Date],1,"days"),"%a")="Mon"

 

 

 

Cw:

 

DateTimeFormat([Date],"%W")

 

 

EoQ is a little fiddly - I suggest:

 

DateTimeFormat([Date],"%b") != DateTimeFormat( DateTimeAdd([Date],1,"days"),"%b")
 && Mod(DateTimeMonth([Date]),3)=0

 

 

Sample attached

danilang
19 - Altair
19 - Altair

Hi @maier_alexander_MO 

 

Just a small addition to @jdunkerley79's post

 

Any time you need to find the end of the month you can use DateTimeTrim([Field], "lastofmonth").  So The EoM calculation becomes

 

 

[Date] = ToDate(DateTimeTrim([Date],"lastofmonth"))

 

 

Dan 

maier_alexander_MO
8 - Asteroid

Hi @jdunkerley79 thank you so much for your quick and detailed answers! I implemented the formulas and it is working perfectly and as desired 🙂

@danilang thank you very much for the addition on how to get the end of the respective month - that will actually help a lot for my workflow as well!

 

I am really thankful for your help on this, have a great day!

Kind regards,

 

Alex

Labels