Is it possible to use a where clause in formulas?

boertjebartje63 shared this question 23 months ago
Discussion Open

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.

Replies (1)

photo
2

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.

photo
1

Hi Alex, thanks for your comment and thinking along!

Via LinkedIn I have sent you more information. I hope I explained it well. It is always hard to describe the problem in a relative short message like this Q and A. But I do appreciate that you gave me a hand!

I hope we will have contact later on. Thanks in advance!

-----> some minutes later ----->

Ahem, while I read your comment again I saw that you mentioned in your [TotalEffortMinutesTomQ1] formula that you wrote to use the SUM function...

You won't believe it, but when I used this function the formula works! :-)

For your information: the concerning formula is: [Sprint Q4-5]=SOM(Mapthemas.[Q4-5]>=1&&SOM(AlgemeneKosten))

So many, many thanks for your comment (also for your articles on Sociamind)!!!

photo
1

------> again some minutes later ----->
Unfortunately I discovered that the 'hallelujah mood' was premature. I still don't get the sum of the general costs for a sprint...:-(

photo
2

If you use SmartRules to create the Q1, Q2 etc properties as I describe earlier all you then need to find the Q1 total is multiply the General Cost by the Q1 number: [Q1GeneralCost]=GeneralCost*[Q1]

Then total these costs on the central topic: [Q1TotalGeneralCosts]=SUM(Descendants.[Q1GeneralCost]

photo
2

And I should have added that if you want the General Cost for a task in a specific quarter related to a specific resource you could use something like: [Q1TomGeneralCosts]=(GeneralCost*[Q1])*(ResourceCost[Tom]>0) on the task topics.

This assumes that Tom also has a Resource Cost. Note that you are not including the Resource Cost in the calculation, just checking that it is greater than zero. If it is greater than zero and it is incurred in Q1 then the General Cost is multiplied by 1 (the Q1 topic property) then by 1 again (from the resource cost being greater than zero), so the General Cost is shown unchanged. However, if the Q1 property is zero or non existent and/or the there is no or zero Resource Cost, the General Cost related to Tom is zero.

Then on the central topic your formula would be: [Q1TomGeneralCostTotal]=SUM(Descendants.[Q1TomGeneralCosts])

photo
1

It is not possible to use a WHERE clause in formulas in the traditional sense. WHERE clauses are used in SQL (Structured Query Language) to filter data based on specific conditions. Formulas, on the other hand, are used to perform calculations and return a single value, or a set of values, based on the inputs provided.

However, there are some ways to achieve a similar outcome in formulas:

  1. Using IF and AND/OR functions: You can use the IF function in conjunction with the AND or OR functions to create formulas that return a result based on specific conditions. For example, the formula "=IF(AND(A1>5, B1<10), "Pass", "Fail")" will return "Pass" if the value in A1 is greater than 5 and the value in B1 is less than 10.
  2. Using the SUMIF or COUNTIF function: These functions allow you to sum or count the number of cells that meet specific conditions. For example, the formula "=SUMIF(A1:A10, ">5", B1:B10)" will return the sum of the values in column B where the corresponding value in column A is greater than 5.
  3. Using the FILTER function: This function allows you to filter a range of data based on specific conditions.

It's important to note that these formulas are used to achieve similar results as the WHERE clause in SQL and they don't use the SQL syntax, but rather the syntax of the spreadsheet software you are using.

photo
2

This response is a little confusing in that it seems to relate to spreadsheet formula language and options rather than the more limited versions available in MindManager.

---