cancel
Showing results for
Did you mean:

# Alteryx designer Discussions

SOLVED

## Dynamically Create Quarter End Dates

Asteroid

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

Highlighted
Asteroid

Hi Jonathan,

If I understood you correctly, you would like to extract only those rows of data matching the dates you mentioned. If so, I believe the attached workflow will do the trick. It does, however, make you input the dates you are looking for manually.

- Mina

Alteryx Certified Partner

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

Asteroid

Thanks @BenMoss for your help, that's nailed it for me.

Also, thanks @mina_ran that was great too, although not exactly the answer I needed, I think I can use that solution elsewhere.

Cheers

Labels