[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 Learn about Wrikeโs killer features and best practices with our Online Training Webinars
Mobeen Tahir Wrike Team member Learn about Wrikeโs killer features and best practices with our Online Training Webinars
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 Learn about Wrikeโs killer features and best practices with our Online Training Webinars
Mobeen Tahir Wrike Team member Learn about Wrikeโs killer features and best practices with our Online Training Webinars
HI Mobeen Tahir
I would like to have a filter on dates that starts from January 1st of the current year and ends on the last day of the previous month.
For example, today is March 15th.
The filter should start on January 1, 2023 and end on February 28, 2023.
Is it achievable?
Pietro Poli This seems achievable. What I can recommend is using two filters for task dates and combined they will give you what you are looking for. See my example below:
To filter for tasks that were due within this year and until last month only, my formula would be like this:
First I will add a filter for task due date = This year:
Second, I will add another filter for task due date = last 12 months with an offset of 1 (this will count only months from this year excluding the current month:
The final formula should look like this:
Combined, they will always give you the tasks from this year and until the end of last month.
Hopefully this helps your use case!
Mobeen Tahir Community Team at Wrike Wrike Product Manager Learn about Wrikeโs killer features and best practices with our Online Training Webinars
Mobeen Tahir Wrike Team member Learn about Wrikeโs killer features and best practices with our Online Training Webinars
Thanks a lot Mobeen Tahir