Dynamically filtering to previous quarter based on date selected by user

Question

Hi,

I am designing a workflow that requires dynamic date-based filtering and calculations. Specifically, the user will select a date, and the workflow will filter data to include only entries that are less than or equal to the selected date. It will then perform calculations based on this filtered data (This is the bit i am struggling with) which include:

* * Calculating values for the quarter immediately preceding the selected date. eg. date = 202310; quarter for this date=4; quarter needed=3.
* Calculating values for the two quarters immediately preceding the selected date. eg. date = 202310; quarter for this date=4; quarters needed=3 and 2.

Given that the selected date will change each month, I need to ensure these calculations are dynamic. Could you provide guidance on how to implement this workflow, including any tips or best practices for managing the dynamic date filtering and calculations?

DianaGalarraga · Answer

here is the workflow if it helps

Dynamic Quarter.yxwz

DianaGalarraga · Answer

Thank you Jhon Smith. This is very helpful however i am struggling to understand how to create a dynamic calculation as i have never done this before. do you mind please providing an example?

jhonnsmith · Answer

Hello

In one of my projects, I had a requirement just like this. I suggest utilizing a combination of date functions and conditional logic in your workflow tool for dynamic date-based filtering and calculations. Here's a quick synopsis:

Date Picking and Filtering: Let users choose the date by using a date picker. Create a filter that only accepts entries with dates that are equal to or less than the chosen date.

Quarter Calculation: To find the quarter for a given date, write a function. For instance, the quarter is 4 if the date is 202310 (October 2023). To obtain the prior quarters, apply a similar reasoning (3 and 2 in this case).

Dynamic Calculations: Apply your calculations from the prior quarters after the data has been filtered. Make sure your logic dynamically modifies the required numbers for each quarter according to the chosen date.

Advice:

To store the quarter computations, use variables.
Make sure the date functions in your workflow tool are dynamic.
Test the computations frequently using various dates to ensure they are accurate.

Regards

Jhon Smith

Brigade Neopolis