[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 Aprenda sobre as funcionalidades e melhores prรกticas em Wrike com o nosso Treinamento Webinar Online
Mobeen Tahir Wrike Team member Aprenda sobre as funcionalidades e melhores prรกticas em Wrike com o nosso Treinamento Webinar Online
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 Aprenda sobre as funcionalidades e melhores prรกticas em Wrike com o nosso Treinamento Webinar Online
Mobeen Tahir Wrike Team member Aprenda sobre as funcionalidades e melhores prรกticas em Wrike com o nosso Treinamento Webinar Online
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 Aprenda sobre as funcionalidades e melhores prรกticas em Wrike com o nosso Treinamento Webinar Online
Mobeen Tahir Wrike Team member Aprenda sobre as funcionalidades e melhores prรกticas em Wrike com o nosso Treinamento Webinar Online
Thanks a lot Mobeen Tahir
I'm looking for an advanced formula that will compare the completion task date versus the due date.ย I'm looking to count how many tasks are marked completed after the due date.ย Is this possible?ย ย
Hi Danielle Jones,
you need to use Day Difference.
Check here: https://help.wrike.com/hc/en-us/community/posts/7307898103191--From-Wrike-Using-the-Days-Difference-DDIFF-function
Let me know if you have more question =)
Pietro Poli I did try this DDIF formula, but it's not working for me.
But I'm not sure this would be the correct formula.ย Here is what I'm trying to calculate.ย I need the total number of tasks that an individual does and completes late (I thought I would use the due date and completion date and compare them).ย Then I need that in a percentage.ย ย Can you help with the formula? Or is this type of ask not available to use in analysis?
ย
Danielle Jones Great question and Pietro Poli thanks for providing the solution. We indeed need to use a Date Difference here but if we want to count how many tasks in total were completed later than their due date we need to also include an IF condition to only count tasks that were completed late. You can use the following formula:
This formula will check tasks that were completed after their due date and sum them up. It is also important to have the following widget filters to avoid tasks that either don't have any due date, or are not yet completed:
If you build it using a column chart you can group the task count by their assignee or any other dimension of your choosing. Final version:
I hope this helps!
P.S: If you change the formula condition from ">0" to "<0" it will count tasks that were completed on time.ย
Mobeen Tahir Community Team at Wrike Wrike Product Manager Aprenda sobre as funcionalidades e melhores prรกticas em Wrike com o nosso Treinamento Webinar Online
Mobeen Tahir Wrike Team member Aprenda sobre as funcionalidades e melhores prรกticas em Wrike com o nosso Treinamento Webinar Online
Mobeen Tahirย I'm still struggling with these formulas.ย I was able to pull the number of tasks versus late tasks.ย I got 73 total tasks and 32 late tasks.ย ย Then I created a formula to get a percentage of how many tasks are on time.ย But this formula seems off.ย 41 tasks should be on time, so my percentage should be 56.16%, not 21.96%.ย Could you help me and tell me if my formulas aren't correct?ย ย
ย
ย
Danielle Jones Can you please try changing the condition from "<0" to "<1" and test? It is possible that you have tasks completed on the same day as their due date, which would give us a 0 for the date difference. Since you have 32 tasks completed late, and 21.92% gives us 16 tasks as completed on time my guess is the remaining tasks were completed on the same day as their due date. Since we need to include them in the calculation, either for late or on time, changing the condition to "<1" will consider those task as completed on time.
ย
ย
Mobeen Tahir Community Team at Wrike Wrike Product Manager Aprenda sobre as funcionalidades e melhores prรกticas em Wrike com o nosso Treinamento Webinar Online
Mobeen Tahir Wrike Team member Aprenda sobre as funcionalidades e melhores prรกticas em Wrike com o nosso Treinamento Webinar Online
Mobeen Tahir That worked for me!ย Thank you!
Hi Mobeen Tahir! I'm looking to break my bar chart by two specific time frames (7.1.22-2.9.23 vs. 7.1.23 vs. 2.9.24). Is that possible with the advanced filtering? It's to compare where we are this fiscal year vs. last year.
Hi Delaney,
Since your time frames are custom we cannot use the "Break by" feature here. "Break by" allows us to split the data into different groupings of days, weeks, quarters and years but we cannot apply two individual custom time frames. Nevertheless, this use case is still possible using custom formulas for each time frame. Below are the steps and screenshots on how to achieve this (please note that my category "PS - Groups" is just a custom field which in your case will be "Property" as I don't have this custom field in my demo account):
This should give you a widget that has a count of tasks across both time frames you've selected and groups them by their property. Let me know if you were able to built it successfully or if you have any follow up questions.
ย
Mobeen Tahir Community Team at Wrike Wrike Product Manager Aprenda sobre as funcionalidades e melhores prรกticas em Wrike com o nosso Treinamento Webinar Online
Mobeen Tahir Wrike Team member Aprenda sobre as funcionalidades e melhores prรกticas em Wrike com o nosso Treinamento Webinar Online
Mobeen Tahir
I have another question.ย I have this task list formula for days late.ย Can I change the formula to have anything that is on time not show in this column?ย Here is the formula I have in that field :DDiff([Days in Task Due Date],[Days in Task Completion Date])
ย
ย
Danielle Jones Would you like to exclude tasks that were completed on time only from the column (i.e. the cell should be blank) or exclude these tasks completely from the pivot table?
Mobeen Tahir Community Team at Wrike Wrike Product Manager Aprenda sobre as funcionalidades e melhores prรกticas em Wrike com o nosso Treinamento Webinar Online
Mobeen Tahir Wrike Team member Aprenda sobre as funcionalidades e melhores prรกticas em Wrike com o nosso Treinamento Webinar Online
Mobeen Tahir I would like for that cell to be blank, if the task is completed on time.ย I still need that pivot table to show me the late tasks.ย ย
Danielle Jones You can try this formula:
With this formula for any active task and for any completed task that was completed prior to its due date you will get an empty cell. For any completed task that was completed after its due date you will get the amount of days it was completed after its due date.
ย
Mobeen Tahir Community Team at Wrike Wrike Product Manager Aprenda sobre as funcionalidades e melhores prรกticas em Wrike com o nosso Treinamento Webinar Online
Mobeen Tahir Wrike Team member Aprenda sobre as funcionalidades e melhores prรกticas em Wrike com o nosso Treinamento Webinar Online
Mobeen Tahir that worked!ย Thank you!
Mobeen Tahirย I have another formula question.ย I would like to do something similar to another report.ย I have a field with Project overages (where I subtract a custom field (Price) from your Wrike field, Actual fees plus a custom field "external expenses, plus a 20% markup.ย I'm trying to figure out if I can get the percentages of those projects that are over.ย I only want projects that are over the mark.ย I tried using the same principles as the time formula you helped me with, but I'm getting an error. Could you help me trouble shoot.ย After using this formula, I go into the field and switch it to a percent.ย ย ย
ย
ย
Hi Danielle Jones,
have you tried to change format of the formula to percentage?
Pietro Poli I did try the percentage and it looks off.ย Plus, I don't want to include projects that come in or under budget, So I don't need the items that are highlighted green anymore either.
ย
Here is the formula, I did try to use a "If" statement with Null, but it won't work for me: Sum([Total Price])-([Total Actual Fees]+([Total External Expense]*1.20))ย
Good day,
I'm looking to be able to create a stacked bar or column chart that shows the percentages of total time logged against a category that contains the word "Billable" vs percentages of total time logged against a category that doesn't contain the word "Billable"ย
I can easily display how much is allocated to each timelog category, but I want to boil that down to just the 2 values based on the category grouping above.
What is the best way to accomplish that.
Hi Danielle Jones
I've created an IF statement with different fields but the result for me is ok.
The 3th column has this formula and calculate the percentage of 2nd column on 1st column
The 4th column has this formula and show the percentage only if it is major of 10%
Here the result
Can be ok for you?
Mobeen Tahirย Hi there! Thank you so much for your solution on February 11th- it worked great. I have one more follow-up question: what formula should I use to calculate the # of hours instead of minutes for "total timelog time spent?" Usually I divide time spentย by 60 but I think I'm missing a parentheses or comma somewhere.ย
ย
cc: Pietro Poli
Hi Delaney Murtaugh, I've asked our Analyze experts to take a look while Mobeen is away. They'll reach out to you separately ๐
Lisa Community Team at Wrike Wrike Product Manager Become a Wrike expert with Wrike Discover
Lisa Wrike Team member Become a Wrike expert with Wrike Discover
Hi Delaney Murtaugh, sorry for delay. do you need help yet?
Pietro Poli The Wrike team helped me out. Thank you!