[From Wrike] Advanced filters with offsets in formulas 🔎
Hi, I’m Mobeen from the Wrike Professional Services team. Today, I’d like to talk about how to use advanced filtering with offset and its use cases.
When using formulas in Wrike Analyze you have the option of choosing different filters based on the field type from a predefined list. Imagine needing to calculate how many tasks had a due date in the past 30 days? We would build an indicator widget to count the number of tasks with a filter for due dates being in the past. Here is how the formula would look:
Selecting the field “Task Due Date” I am able to select a time frame for the date being in the “Last 30 days”.
Wrike Analyze provides different options for filters as we can see in the screenshot above. You can select exact dates from the List or a range of dates from the Calendar. The Time Frame option lets you select more dynamic ranges like “Last 30 days” which would always update every day reflecting the last 30 days from the current day.
What if I want to calculate the number of days which is not in the options of Time Frame? This is when we would leverage the Advanced section at the bottom of the filtering categories.
Switching over to the Advanced category while having “Last 30 days” selected in the Time Frame will let you see exactly how it is calculating the last 30 days. Without needing to edit the code too much, we can still use it to configure our own custom ranges in the filter. Before we do that, let’s see what it means.
count: This is the number of days you would like to count.
offset: The count always starts from the current date, using offset we could skip several days to build our own ranges.
If I wanted to calculate the number of tasks that have a due date in the last 10 days, I would do the following:
· Create the formula to calculate tasks and a filter for task due date
· Select the last 30 days from the time frame part of my filters in the due date
· Switch over to the advance and change the count from 30 to 10
And that’s it! This formula will now calculate the number of tasks that have a due date in the past 10 days using our own advanced filtering option.
The quickest way to get started on an advanced filter is by selecting the most relevant option from the Time Frame category and editing the values for the count and offset in the advanced section. This way you won’t have to worry about the correct formatting or syntax of the code.
Let’s look at how the offset can help you. Imagine having to calculate the tasks that have been due in the last:
· 10 days
· 11-20 days
· 21-40 days
To make sure no tasks are counted in two ranges, we would need to use the offset option and avoid duplicates.
We already saw how to use the advanced filter to calculate the last 10 days, I will now add the other two ranges and combine this in a column chart.
My filter for 11-20 days would look like this:
To avoid the first days 10 days that I am already calculating in the first formula, I would need to offset my range by 10 days and further count 10 more days. This filter will help me count my tasks that have a due date in the last 11-20 days.
Similarly, to calculate the tasks with a due date in the last 21-40 days I will need to offset by 20 days and count further 20 days:
My final column chart would give me the tasks due in all ranges without having any duplicates between the three ranges:
Hopefully, this helps you understand how to use advanced filtering and get some ideas on its use cases. Some ways this can be used are for:
· Calculating how many days tasks have been overdue divided among different ranges
· Monitoring upcoming milestones in the next few ranges
· Checking how many completed tasks were late in different ranges
Can you think of other use cases for using advanced filters? Be sure to let us know below. See you in the next post!
Mobeen Tahir Community Team at Wrike Wrike Product Manager Узнайте о самых популярных функциях Wrike и советах по его использованию
Mobeen Tahir Wrike Team member Узнайте о самых популярных функциях Wrike и советах по его использованию
Hi Mobeen Tahir, thanks for the tip. We have a use case that this advice will help. We want to show the number task overdue, but do not want to count today, so your explanation of offset will really help us.
Kevin Thalacker great to hear that. Excluding "Today" from the count is a perfect use case for advanced filtering with offset.
Mobeen Tahir Community Team at Wrike Wrike Product Manager Узнайте о самых популярных функциях Wrike и советах по его использованию
Mobeen Tahir Wrike Team member Узнайте о самых популярных функциях Wrike и советах по его использованию