Dynamically filtering to previous quarter based on date selected by user

[DianaGalarraga]

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:

1. • 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?

[aatalai]

@DianaGalarraga try this, as you date is in the format YYYYMM, in the formula tool put the following expression

iif(ceil(tonumber(right([date],2))/3) -1 !=0,ceil(tonumber(right([date],2))/3) -1,4)

Let me know how you get on

[DianaGalarraga]

Thank you. I'm quite new to apps - when i add the formula you provided, it created a new column with the quarter that i am looking for however it is not filtering for data within that quarter. I want to dynamically filter so that i get data for the previous quarter from the data selected.. e.g if the user selects 202310 which falls in Q4 i want data for 2023 Q3 to appear. 

This is the output i get when i add the formula tool mentioned

[jhonnsmith]

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

[DianaGalarraga]

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?

[DianaGalarraga]

here is the workflow if it helps