Dynamically Create Quarter End Dates

[Sidey1978]

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

[mina_ran]

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 · Accepted solution]

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]

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