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

Dynamically Create Quarter End Dates

Sidey1978
8 - 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

3 REPLIES 3
mina_ran
8 - 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

BenMoss
ACE Emeritus
ACE Emeritus

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

Sidey1978
8 - 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