linking formulas along with categories

7.84K viewsFormulas and Functions
0

It occurred to me that the ability to create categories in one matrix and then link them into another matrix can act a little like ‘Classes’ in object oriented programming. You have your structure (Categories and Items/Groups) and methods (Formulas). The ability to have default data that the linked categories can reference if they choose (via inter-matrix formulas) is also handy, but it is good that it is kept separate unless explicitly linked.

Once you’ve defined your categories and their rules, you could combine them in separate matrices to create a compound ‘object’, which inherits the structure you link in. The structure of a given linked category will reflect updates to any of the copies in the various matrices that use it. One that you are arbitrarily treating as a master copy will update all other instances. However, the same is not true of formulas.

When a category is linked, the formulas do not come with it, and must be copied on the clipboard. If changes are made in either the ‘original’ or the ‘copy’ (*) they are not reflected in the other instances.

While I believe there will be situations where the model will benefit from the ability to change the formulas that operate on a linked category (sort of like overloading an object method), there will also be times when a linked formula will allow you to reduce the number of places that you have to replicate changes.

What if the formulas that are defined in a category could be either linked or copied and the default action were based on an application default. Formulas that are linked would show the same Link icon as categories next to their checkbox or summation icon. The user would also need a way to break a link (copy) or re-establish a link (replace).

Attached (hopefully) is a model with helps illustrate the behavior of formulas. (Just delete the Quarters category from Sheet1 and Sheet2, and then link Quarters to them again from clsQuarters matrix.)

Steve

* – the quotes are to acknowledge that all linked instances of categories are treated as co-equal by QM

Attachments

classes.model
0

SteveB

I had seen that popup on the Category link, but hadn’t thought about it. Yes, a popup on the formula (each) would do nicely for quick access. The only gotcha is that the tooltip disappears after about 5 seconds. This could make it frustrating to read through.

Regarding the ability to pull out the formulas, Yes, there would be the problem of separating the behavior from the structure, forcing the latter to be reconstituted.
However….

You have touched on something that needs to be thrown into the ring for discussion, even though it will probably drive the Quantrix folks nuts. 😉
If we truly believe that our categories and formulas encapsulate logical building blocks, from which we will construct applications, then we will be forced to confront the possibility of storing them outside of our models and a) importing or b) linking the physical files.
Since you also mentioned industry-specific web-sites, you may already have in mind XML files that a model file could reference using a URL. Then you could take advantage of live updates to industry-standard calculations. (It is fortunate Quantrix has already worked out an XML-based format for Saves.)
Of course, in keeping with Quantrix’s stated desire to allow the user to keep everything on their desktop, the user would also need the option to import the file from the URL, rather than just linking to it.

Steve S

0

Steve S.
I don’t think that additional functionality would need to be added to see the detail behind the user defined formula. The user could simply follow back to the original formula by using the hover over the link feature that already exists.

How about the ability to pull the formulas out some how to make available on in industry specific web site (link)? Just carrying this OOP analogy just alittle further. Not sure how that would work since the new user of the user defined formula would have to set up the data matrix exactly as the submitting user did.

Steve B.

0

John,

So, if I understand what you’re suggesting, the ability to have ‘summary-like’ functions, but to have finer control over

1) which items in a category are included, and
2) what operation is done to them.

Yup, I can see that. If I got that right, that kind of takes us back to user-defined formulas that are copied like the summaries are. Except in this case its coming from the other direction. (So, instead of saying ‘which’ items to do ‘what’ with … we would be saying ‘what’ to do, but ‘which’ items to leave alone.)

Is this close?

Steve

0

SteveB

Good point about the name — a well-named function can go further toward clarity than a lot of details. This especially true for well known algorithms in a given industry, when the algorithms are not built-into QM (and don’t necessarily need to be).

Then if the reviewer wants to see the details, they could find them either as a comment, or some sort of expanded view that is visible on-demand only.

And the abililty to ‘overload’ it further, as you suggest, will be handy too.

Steve S

0

This makes sense to me because there are often times when the constraints of automatic summaries make them inappropriate (in my case, for example, when I need to exclude KEY or INDEX items from the summary).

What would be nice from my point of view is the possibility of manually gaining all of the functionality of automatic summaries–tagging some functions as “summary” functions of the category. This would be a key part of that.

John

0

Well the possibility of showing the users definition as a remark in the formula line could be a feature.

I just love creating work for our Quantrix friends.:)

A user defined formula would also allow for manipulation of the data as such:

Catagory.item5.user_defined1=user_defined1*some other data.

It might actually create a more readable formula from a non-pro users perspective. For example, I have a user defined formula that does all the heavy lifting to calculate the trajectory of a missile. From that point on, a less advanced user could then manipulate that result. They could just see the user_trajectory value and then go from there and not having to write out the whole thing again which they may or may not understand.

Steve

0

SteveB
Yes, a hint of that thought occured to me after I had posted the PS about the summary formula. It would certainly be a way to accomplish a similar result by leveraging the existing code in QM. Not such a bad idea, really. Slightly more complex? Maybe, but not too much so.

The only real negative I can think of might be that the formula used as the definition of the function might be less ‘visible’ (from an Auditing perspective) than regular formulas.

Steve S

0

I understand Steve’s point about linking user defined formulas between matrices. Other formulas are available for groups and that should cover a bunch of requirements. I believe it has some merit.

Another possible way around this would be to create a user definable “function” and make that available as a drop down. The formula would then carry along with the categories just like the summation formula does. This may actual be a more complex solution but it is an alternative although it can piggy back on the functionality already in place with the canned formulas.

(category.item1+category.item2/category.item3)*category.item4*constant=user_formula1

In the formula area it would look like this:
user_formula1.category.item5=user_formula1

Steve

0

Hi Steve,

I my model building… outside of summary formulas I have never had the urge to have a non-summary formula follow a category to other linked matrices.

But I am far from the final authority on this subject. I am going to invite a couple of other Quantrix users in on this discussion and see if I can get their input. Stay tuned to this thread.

-Mike

0

PS – I am pleased to note that the Summation formula IS automatically copied to the matrices into which the Category is linked. This closely mimics the linking of formulas that I mentioned, except that I am specifically proposing the ability to do this with user-defined formulas.

Steve

0

(…continuing separately, because I seem to have hit the Enter Key prematurely)

Perhaps I should have come up with an example other than a simple Sum, because the contents of the formula is really not relevant to the point I want to make.

Lets say that I was defining a more complex relationship among the items (and groups) in the category that is going to be linked. And lets say that the resulting formula is one that is not covered by any built-in feature or function of QM.

I can still see a value in being able to define that formula once and have it optionally follow the category to wherever it is linked. Does that sound like a useful feature?

Steve

0

Mike,
Yes, I see that, and if I want summaries it would certainly make more sense to use that than do it manually like I did in the first version of the model.

0

Hello Steve – Thanks for your post:

With Quantrix 1.2, automatic summary items give you some of the functionality you are looking for. Automatic summary formulas are linked to the category and reside in all matrices that the category resides in.

I have attached a new version of the model with an automatic summary inserted in the Quarters category.

You can insert automatic sums by clicking on the category name, and either using the sum Icon on the toolbar or using Insert–>Summary Item from the menu.

-Mike

0

To follow up…

The choice of the categories Reps, Regions, or Products in the classes.model is really irrelevant and was made arbitrarily. The key thing is that the 2 matrices aren’t related except by the need to have the same structure (4 quarters) and behavior (do a sum).

Steve

Latest Questions

Wrap text in scripted buttons? 1 Answer | 0 Votes
Claculate product 2 Answers | 0 Votes
Sum by group in the same matrix 4 Answers | 0 Votes
Constrain input – varchar 1 Answer | 0 Votes