[From Wrike] Advanced filters with offsets in formulas ๐
Mobeen Tahir Community Team at Wrike Wrike Product Manager Infรณrmate sobre las funciones y prรกcticas recomendadas de Wrike
Mobeen Tahir Wrike Team member Infรณrmate sobre las funciones y prรกcticas recomendadas de 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 Infรณrmate sobre las funciones y prรกcticas recomendadas de Wrike
Mobeen Tahir Wrike Team member Infรณrmate sobre las funciones y prรกcticas recomendadas de Wrike
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 Infรณrmate sobre las funciones y prรกcticas recomendadas de Wrike
Mobeen Tahir Wrike Team member Infรณrmate sobre las funciones y prรกcticas recomendadas de Wrike
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 Infรณrmate sobre las funciones y prรกcticas recomendadas de Wrike
Mobeen Tahir Wrike Team member Infรณrmate sobre las funciones y prรกcticas recomendadas de Wrike
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 Infรณrmate sobre las funciones y prรกcticas recomendadas de Wrike
Mobeen Tahir Wrike Team member Infรณrmate sobre las funciones y prรกcticas recomendadas de Wrike
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 Infรณrmate sobre las funciones y prรกcticas recomendadas de Wrike
Mobeen Tahir Wrike Team member Infรณrmate sobre las funciones y prรกcticas recomendadas de Wrike
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 Infรณrmate sobre las funciones y prรกcticas recomendadas de Wrike
Mobeen Tahir Wrike Team member Infรณrmate sobre las funciones y prรกcticas recomendadas de Wrike
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 Infรณrmate sobre las funciones y prรกcticas recomendadas de Wrike
Mobeen Tahir Wrike Team member Infรณrmate sobre las funciones y prรกcticas recomendadas de Wrike
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!