Using the "Count" option in formulas

  • 1
  • Question
  • Updated 3 years ago
Just discovering the joys of formulas but failing to find the answer in the help file. I am in the process of creating a UK 2015 General Election map. I have sub topics of consituencies off of main branches of  geographic areas. For each geographic area It seems straight straightforward to do arithmetic operators with numerical properties on the constituency topics, however what I would like to do is count the number of MPs of each party. On the consituency branch I have created a list copic called MP, with preset values for each party (Lab, Con  etc.)



In the geaographic area topic, I would then like to count the number of constuency topics where the value of the MP property is Lab, and then Con etc., with a separate property counting each value on the list. My attempt below doesn't generate any errors but doesn't get any result either.


Is it just my query syntax or am I completely barking up the wrong tree trying to use the Count function to do this?

If using Count is not going to work, can anybody suggest an alternative way of achieving my objective? One way that that came to mind was having numeric properties for each party on the constituency topic which had a value of either 1 or 0, but this seems a verylong-winded and in-elegant way of doing it. Any suggestions?
Photo of Angus McDonald

Angus McDonald

  • 15 Posts
  • 1 Reply Like
  • skunnered (as we say in Scotland ;-)

Posted 3 years ago

  • 1
Photo of Nigel Goult (Olympic & MIVAP)

Nigel Goult (Olympic & MIVAP)

  • 344 Posts
  • 133 Reply Likes
Hi Angus,

From what I can see the COUNT function will only count a Property of a numeric type that has a value. So Number/Integer/Percentage/Currency will work.

Once you have any other type the Property seems to be ignored in the count.

Could you not have a numerical Property "MP Lab" and then put a "1" in it? Then you could use the count. You could also just autocalc all the "MP Lab" properties.

Hope this helps.

Regards

Nigel

Have you got your MAP? - MAP for MindManager, the ultimate companion to MindManager for Windows! Learn more at www.mapformindmanager.com
Photo of Angus McDonald

Angus McDonald

  • 15 Posts
  • 1 Reply Like
Thanks for the suggestion Mr Goult. The best get-around in the short term seems to be indeed to have a Constituency topic property called "MP - Lab" and give it a numerical value of 1. I can then have a formula property in the geographic area parent topic called "MPs - Labour" that can either SUM the total value of all "MP - Lab" properties in the descendants, or COUNT the number of times they occur. However I now realise that I don't  have to have a numeric property for each party on every constituency sub-topic, most of which will have a value of zero anyway (as by definition (and as Sean Connerry said in Highlander) "there can be only 1 (with 1!) ;-). Instead I just need the SUM/COUNT property for each party on the parent topic and then just change the name of the property field on the consituency topic from "MP - Lab" to "MP - Con" (or whatever) to match the party of the MP if it changes at the election.

A bit less work than I thought but as I said, not very elegant and rather "arse about tit" to change the property name rather than the value to reflect the change in the real world! Also from an information cartography point of view I want the information dispalyed as part of the topic to make the most sense to the viewer as well as enabling the "back-end clever automatic calculation on the fly" stuff. An MPs party is not a numerical value in the real world.

Just had a brain wave as I type this though. I could still have my original drop down list of parties as well as my numeric field to make the formula work, but make my numeric field hidden from the viewer? That is probably the best compromise (if it works).

Mr Mindjet if you are listening a couple of "asks" regarding topic properties:-

1. Why give us the ability to have a defined list of text strings as property "values" but then deny us the ability to do anything even basic arithmetic with them like count the number of times that value occurs?

2. Whilst on the subject, why not give us the ability to sort topics by the values of associated properties, just like you can do with a spreadsheet?

2. Ditto the above for Smart fill, that will only work on a numeric field.

4. Whilst on the subject of Smart fill, could we have Smart Line / Text Colour based on topic property value as well?

Thanks

Angus
Knowledge Mappers - Mapping Your Knowledgescape

PS - Still taking any other suggestions from the community on how best to achieve my aim of counting MPs based on topic properties.
Photo of Alex Gooding

Alex Gooding, Champion

  • 827 Posts
  • 208 Reply Likes

One simple option would be to use the Map Index rather than the map itself to count the MPs. You could use text tags, icons or topic shading to denote party and region and the index will keep a count of these. You can then filter the map to show, for example, the number of Labour MPs across all regions, the number of from each part in a particular region or any combination of these.

The problem of course is that this count is kept only in the index and not in the map.

Photo of Angus McDonald

Angus McDonald

  • 15 Posts
  • 1 Reply Like
Alex

Many thanks for your suggestion. I am using tags in the map as well so I hear you about the index, however there are drawbacks to using tags to count "things" as it means you can only use the tag on that type of "thing", otherwise you will be overcounting. For example I may combine MP and constituency topics on the same map, but if both are tagged with the "Labour Party" tag then I have a problem.

Anyway I am using the making of this map as an exercise to discover and explore the topic properties functionality as I haven't really used it "in anger" before. As usual my faltering progress is due to a combination of my own personal ignorance and the sometimes counter-intuitive / idiosyncratic machinations of MindManager :-(. (As Mr Goult will testify even a power user of his longstanding experience still discovers new surprises!) Given that there are over 650 constituency topics and the same number of  MP topics, it's a lot of manual labour involved in creating each property so I have to make sure that it's going to be worth all the effort in the end.

For those of you following this thread my latest thinking is that I now realise I don't have to have an extra property just to record the party as a 1. I will be adding the majority by which the winning canditate won (ie. a numeric property) so by adding (Lab) or whatever to the property name then the number of MPSs of each party can be COUNTED separately in the parent topic. It's still the wrong way about by recording the variable data as a field name rather than a value but it will do the job.

Mr Mindjet if you are still listening I have another suggestion for improving topic properties.

5. I can use the "Find" function to find properties containing a particular text string but I cannot seem to do a "Replace" (the option is greyed out in the "Find & replace" dialogue box). Thus when I come to update the map for an election in another year I will have to manually alter all the property names. Currently I have added the year to the property name so that the user can add the same data from different years to the topic as another property if they would like.

6. I cannot paste a property into multiple selected topics at one time. Thus I have to add each property to each topic manually one at a time. As I said above there are over 650 topics so it's a lot of manual labour. Is this an oversight, a glitch or a technical impossibility to implement?

Mr Goult - Perhaps you could explore some of these as new modules for your MAP tool?

Signing off for now but I will keep you posted on how it goes. Once again thankyou for your time Alex.

Regards

Angus
Photo of Alex Gooding

Alex Gooding, Champion

  • 827 Posts
  • 208 Reply Likes

Hi Angus,

While you appear to have come up with a solution that is better suited to your purposes - and I agree with all your comments about how Mindjet could make the use of topic properties a lot easier - there are actually a couple of ways to deal with the issue you identify in relation to using tags.

It's true that in your example the total will be over-counted in the unfiltered map if you use the same tag for  Labour (for example) in relation to topics both for parliamentary seats and constituency issues. However if you also have separate tags for parliamentary seat and issues topics all you need to do is filter both for parliamentary seats only and Labour only. If you do this the count of the Labour text tags in the map index will be adjusted to show just the count of Labour MPs

Alternatively you could establish separate tag or icon groups with one containing icons or tags for MP's party affiliations and the other for issues by party/affiliation. The only caveat is that you can't use the same tag or icon twice, even in different groups. It might be simplest to use icons for one or both groups, as while icons have to be unique their names do not. The advantage is you don't have to filter the map; even when unfiltered the map index will show the correct totals.

The image below shows the "result" of a simple six-seat election as recorded in a map using the second option:

The map index would look like this:

While this approach isn't as versatile as using topic properties it has one great advantage - you can apply icons and tags easily to groups of highlighted topics. This is obviously something to consider if you have already created the topics.

One thought though, if you do want to use topic properties - if you haven't created the constituency topics , why don't you save topics with the appropriate topic property as map parts? Then you could insert from the map part pane say a topic with a "LAB" property when you wanted to create a seat won by Labour, and so on. Of course this approach doesn't make it easy to import constituency and or winning MP lists by party when these are made available online and automatically apply the appropriate map part during the import process, but someone may be able to come up with a macro to do this.



Photo of Nigel Goult (Olympic & MIVAP)

Nigel Goult (Olympic & MIVAP)

  • 344 Posts
  • 133 Reply Likes
Hi Angus,

Mr Goult here ;-)

We are just wrapping up MAP 1.7 which should be out by the end of this weekend. We are already working on MAP 1.8 which includes two new pieces of functionality focused on Topic Properties. One of these includes the capability to copy Custom Properties, Formulas or both from Topic to Topic. in fact I was working on this yesterday.

Obviously you are working with this scenario now and I wondered if you wanted to "test" an early version of 1.8?

Let me know if you do and I'll send a link.

Regards

Nigel

Have you got your MAP? - MAP for MindManager, the ultimate companion to MindManager for Windows! Learn more at www.mapformindmanager.com