Is it possible to use a where clause in formulas?
I am trying to make a (kind of) dashboard in which I want to show calculations of general costs by quarter.
My expectation was that I could make a formula like:
[Sprint Q1]=GeneralCosts[Q1] where Q1 is a source. (*)
I game to this idea when I saw a (similar) example in the mindmap Knowledge Dash Formulas and Calculationsmmap.mmap (mindmanager.com) (also see added picture).
However, in my dashboard it doesn't work like it does in the mindmap.
I realize that there is a difference with my 'dashboard', because I want to get values/costs from all over the map and not just in that particular topic. But what I want is only the costs for Q1.
I also tried to sum up properties from the quarter Q1, but that doesn't work either. I used the 'Sum', but all the same...:-(
Is it possible to use a kind of where clause with which you can sum up costs by several quarters?
Thanks in advance!
(*) I probably did not write GeneralCosts in the right way because I use a Dutch language.
This is a complex area, but there is a way to set up an If argument in a formula, as I describe in this article: https://sociamind.com/2021/03/28/logic-time-and-money-more-mindmanager-formula-surprises-part-2/.
You can also set up an If argument using SmartRules, which might be a better starting place in your case. I would like a bit more information about what you are trying to do, but one option would be to set up four numeric topic properties Q1, Q2, Q3 and Q4.
You could then set up a SmartRule which basically captured all tasks with a start date and due date within each Quarter and would then add the number "1" to the relevant topic property. So, for example, for Q1 2023, the trigger would be a task start date after 31 December 2022 and a task due date before 1 April 2023. Any task within these parameters would have "1" added in the Q1 topic property.
Then you could simply multiply out the parameters, so your formulas would be:
[EffortMinutesTom]=EffortMinutes[Tom]
[EffortMinutesTomQ1]=[EffortminutesTom]*[Q1]
So, you would only get a number greater than zero when the effort was expended in the first quarter which would give the value of 1 in Q1.
To total all the Effort Minutes for Tom in the first quarter across the map, you would then add the following formula to the Central topic:
[TotalEffortMinutesTomQ1]=SUM(Descendants.[EffortMinutesTomQ1]
You then have to duplicate these formulas for each quarter.
However, there is one obvious problem with this approach - it can't handle longer tasks which span across two or more quarters. There is no easy solution for this, other than breaking these tasks into shorter ones which fit within each quarter.
Edit: I’ve used EffortMinutes as per the formula examples you referenced, but the same principles should apply to general costs.
This is a complex area, but there is a way to set up an If argument in a formula, as I describe in this article: https://sociamind.com/2021/03/28/logic-time-and-money-more-mindmanager-formula-surprises-part-2/.
You can also set up an If argument using SmartRules, which might be a better starting place in your case. I would like a bit more information about what you are trying to do, but one option would be to set up four numeric topic properties Q1, Q2, Q3 and Q4.
You could then set up a SmartRule which basically captured all tasks with a start date and due date within each Quarter and would then add the number "1" to the relevant topic property. So, for example, for Q1 2023, the trigger would be a task start date after 31 December 2022 and a task due date before 1 April 2023. Any task within these parameters would have "1" added in the Q1 topic property.
Then you could simply multiply out the parameters, so your formulas would be:
[EffortMinutesTom]=EffortMinutes[Tom]
[EffortMinutesTomQ1]=[EffortminutesTom]*[Q1]
So, you would only get a number greater than zero when the effort was expended in the first quarter which would give the value of 1 in Q1.
To total all the Effort Minutes for Tom in the first quarter across the map, you would then add the following formula to the Central topic:
[TotalEffortMinutesTomQ1]=SUM(Descendants.[EffortMinutesTomQ1]
You then have to duplicate these formulas for each quarter.
However, there is one obvious problem with this approach - it can't handle longer tasks which span across two or more quarters. There is no easy solution for this, other than breaking these tasks into shorter ones which fit within each quarter.
Edit: I’ve used EffortMinutes as per the formula examples you referenced, but the same principles should apply to general costs.
---