NEED HELP!! | Make workflow dynamic | Update values based on month/quarter of the year.

[RajatK]

Hi All,

I am new to Alteryx and need some help with my project. Listing the scenario and details below: 

The data looks something like this (also, attaching the file here): 

Calculations or checks are made based on the quarter of the year/month of the year, as below:

As shown above, the check 1 comprises ~> if K2<= Sum of Apr-21, May-21 and Jun-21. In the next quarter, it would be changed/updated to check of ~> L2<= summation of Jul-21, Aug-21 and Sep-21. 

{L2 is currently empty/blank since, no we haven't reached the end of the quarter yet). 

This can easily be done in Excel, however, since I'm new to Alteryx, could you please suggest a way on how I could do this dynamically. For now, I've used a Formula Tool and hard-coded the values.

Attaching the data and the client's fiscal year calendar for your reference. 

Kindly help me with the solution and a humble request to attach the package/workflow, so I could get a clarity on how to go about it. 

Thank you in advance!!  

@atcodedog05 , @VictorLeonis , @john_watkins , @ncrlelia please help!!

[phottovy]

Hi @RajatK 

Attached is a workflow that I think accomplishes a lot of what you are looking for. I recommend getting comfortable with the Transpose and Cross Tab tools for something like this. They allow you to pivot your data to make period comparisons much easier. I wasn't exactly sure where the check formula would go for Q2 but here is what my result currently looks like:  

[ncrlelia]

Hi @RajatK,

Please see attached my suggested solution.

Hope it helps.

Cheers,

Lelia

[john_watkins]

I keep seeing a recurring thing here in the community where data sets are used while in an "Excel" column format.  Often the answer is much easier when you convert to a row/database format for your data.  Create a field that is useful as either Q1, Q2, etc. or YYYY-Qx format and sum that way grouping on your new field or doing a calc or however you wish to tackle it.

ToString(Ceil(ToNumber(DateTimeFormat([Date],'%m'))/3))

[danilang]

Hi @RajatK 

If you've already started on a solution and have a partial workflow, please include it as well as your sample data.  Most of the time it's easier to fix an existing workflow than create a new one.

Dan