Dynamically Create Quarter End Dates
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Hi gang
I have a sample list of customer billing information for a year. I need to extract the invoices into quarters, however we have strange quarter ends due to our financial year end. The quarterly periods I need are:
2018-10-31
2018-07-31
2018-04-30
2018-01-31
The other issue is that I need to run this report quarterly, always looking back over the past twelve months. So the quarters would change based on the new twelve month period, i.e the next time it would be the twelve month period to 2019-01-31.
Any help would be greatly appreciated!
Cheers
Jonathan
Solved! Go to Solution.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
You can just use the month field to generate the quarter period I think, and use the month to acknowledge what year should be prefixed too...
IF SUBSTRING([DATE],5,2) = "11" OR SUBSTRING([DATE],5,2) = "12"
THEN "Q1 "+TOSTRING(TONUMBER(LEFT([DATE],4)+1) )
ELSEIF SUBSTRING([DATE],5,2) = "01"
THEN "Q1 "+LEFT([DATE],4)
ELSEIF SUBSTRING([DATE],5,2) = "02" OR SUBSTRING([DATE],5,2) = "03" OR SUBSTRING([DATE],5,2) = "04"
THEN "Q2 "+LEFT([DATE],4)
ELSEIF SUBSTRING([DATE],5,2) = "05" OR SUBSTRING([DATE],5,2) = "06" OR SUBSTRING([DATE],5,2) = "07"
THEN "Q3 "+LEFT([DATE],4)
ELSE "Q4 "+LEFT([DATE],4)
ENDIF
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator