Dynamic Quarter Date

[Taxautomation]

Hi,

Is it possible to have a filter or formula bring up the current quarter? Like if I run this in April next year I want it to bring me Q1 numbers even if the data below has numbers booked in April. As of right now I have to go into my workflow and uncheck the previous quarter and check the quarter I want to bring. It would be cool if this could be done by Alteryx. Like if there is a way to have the select tool check and uncheck fields based on a formula, or some other tool arrangement that gets this done.

My data looks like this:

01_	02_	03_	Q1	04_	05_	06_	Q2
12345	12345	12345	=sum(01+02+3)	04	05	06	=Q1+sum(04+05+06)

[alexnajm]

I would look at Transposing the data, Filtering to the needed quarters, and Crosstabbing back to make these calculations dynamic!

[CoG]

The Dynamic Select Tool may also be useful here. If you include what your output is supposed to look like, that would be helpful in providing more specific suggestions for a solution.

[Taxautomation · Accepted solution]

These are all valid solutions and I wish I had looked at them prior to spending a lot of extra time figuring out date functions on the formula tool. Sharing below in case it helps anyone else in the future.

I Ended up using a formula tool and adding a new column with this:

If

DateTimeMonth(DateTimeAdd(DateTimeToday(), -1, "Month")) <= 3 Then [YTD Q1]

Elseif

DateTimeMonth(DateTimeAdd(DateTimeToday(), -1, "Month")) > 3 AND DateTimeMonth(DateTimeAdd(DateTimeToday(), -1, "Month")) <= 6 Then [YTD Q2]

Elseif

DateTimeMonth(DateTimeAdd(DateTimeToday(), -1, "Month")) > 6 AND DateTimeMonth(DateTimeAdd(DateTimeToday(), -1, "Month")) <= 9 Then [YTD Q3]

Elseif

DateTimeMonth(DateTimeAdd(DateTimeToday(), -1, "Month")) > 9 AND DateTimeMonth(DateTimeAdd(DateTimeToday(), -1, "Month")) <= 12 Then [YTD Q4]

Else "Check"
Endif

Note that the YTD fields are just the sum of all the relevant months on that quarter.

[CoG]

That may not be the most efficient solution, but that is a perfectly valid one! Well done. You put in the time and effort and solved the problem yourself!

[KGT]

Because I can't leave it like @CoG can...

Nice work and that formula does not need updating as it works. What I use for this function is CEIL(DateTimeMonth(.......)/3). This will take the integer month, divide it by 3 and then round up to the nearest integer, giving you your Quarter number. Here for more info...