Meditation on timelines

494 viewsModel DesignTimelines

Hello all,

I\’m often hesitating which strategy to adopt for timelines in my model. I’m currently working on a big planning model, which contains Actuals, Budget, Short Term Plan, and Long Term Plan. So, there are different timelines, and the source of the values and the formulas differ depending on which part of the timeline is concerned.

There a several criteria to choose the best solution:

  • Ease of update (unfortunately, the years change every year, budget becomes Actuals, first plan becomes budget etc.). Ideally, I do not want to destroy any data, keep all timelines in sync and consistent, so nice would be to have a Global configuration table with Start Year / End Year for the different time lines.
  • Easy formulas when fetching data from one matrix to another.
  • Easy formulas when applying logic only to a group of years.

I have identified three possible approaches:

  1. One Year category for the entire model, having groups inside the category for “Actuals”; “Budget”, “Plan”, “Future” or the like. The category is manually defined, and groups must be made manual. In matrices where we only have “Actuals”, the rest of the years is empty. Writing formulas is easy, as the can just be applied to Actuals, like “in Actuals, := …”. Fetching data is also easy, as there is only one category. => Tough manual, this seems to be a clean approach for small models.
  2. Several Year categories, e.g. one for Actuals, one for Future, one for AllYears. These categories are automatically generated from GlobalSettings. Therefore, we cannot use groups. => Update: very easy (automatic). Formulas fetching: complicated, as we always need a select, as Quantrix does not recognize the equivalence of Year “2025” in one category versus another category. Applying formulas to only one group also is complicated, e.g. an if can be used, or criteria in the select, always referring to the year settings globals.
  3. Using the timeline feature of Quantrix, which was invented to exactly address this problem. As Timelines and groups also do not work together, we also need to create different timelines. => Update: Unfortunately, timelines can not update dynamically from a GlobalVariable. So, every Timeline in the model has to be updated manually, and all has to be in sync (sounds easy, but if you have 20+ timelines in a model, this gets tricky). Also, you must absolutely not use absolut years in all your formulas, which is tricky. I tried out some techniques in the demo file, but there are cases where it’s difficult.

I still have no clear preference over these three techniques. I somehow feel technique 2 is the weakest (despite of being fully automatic, what is a big plus, and would be a requirement for every solution), as it combines the biggest disadvantages from solution 1 and solution 3.

Solution 1, although completely manually, is probably the cleanest for lot of applications, this should work for all simple models. I would probably always try this first.

If one really feels the need for different timelines, then option 3 should be considered.

How to improve Timeline features?

These is a temporary feature wish list, which might improve some of the shortcomings above.

  • Allow for groups even in generated categories.
  • Allow for groups in Timelines
  • Allow for dynamic definition of Timelines (e.g. start, number of years).
  • Allow for dynamic values in the “in-Statement”, e.g. “in Globals::BudgetYear .. Years[Last] , := ….”
  • calculation-range in formulas is currently set to the minimal overlap in all timeline ranges. It might be helpful (especially in recursions) to first calculate on the entire timeline, and then restrict only the result onto the minimal overlap. This would allow to create recursion formulas.

What do you think?

As most of you will know these kind of problems, I would really be interested in your comments:

  • what is your preferred approach – 1, 2, 3 – or do have an approach not mentioned above? Can you contribute it to the community?
  • do you have other / better solutions for the formula examples in the demo model? Are there better / simpler ways to handle groups?
  • what do you think of the above feature requests? Would they help you? Do you need other features to make life easier with timelines?

I’m sorry that this post got so long. But I think Timelines are such a fundamental concept in probably nearly all models, that they deserve a bit of attention.

Looking forward to discuss this!


P.S.: To take a look at the 3 concepts in the demo model, use this three perspectives:

Gilbert Answered question April 27, 2024

Re – Automatic update of Timelines:

For the ones using the Timeline – Feature (Solution 3), I found a workaround for automatically define the Year-Range of timelines. While it is true that the properties “StartYear” / “Number of Years” are NOT accessible via script, astonishingly, it is allowed to add/delete items in automatic timelines. If done in the correct order, the timeline can be modified as wished. I join a little demo example you can include in your models.

Gilbert Answered question April 27, 2024

Your three approaches have Fusion* matrices with almost the same structure and calculate the same Items: CumAll, CumFuture and FromPrevious. Therefore, my formulas are suitable for all your approaches. The formula for Value in the first approach:

FusionMan::Value = ActualsMan::Value + FutureMan::Value

I prefer to create parametric models in which the matrix structures are defined by expressions for the GI-module based on the input data of the model. I build models so as not to confuse either myself or users. For example, if I enter the ActualsMan:: matrix into the model to store the actual input data, I will never enter a structural element into the ActualsMan:: matrix named Future. This is simply illogical. A symmetric picture with FutureMan:: relative to structural elements named Actuals.

I use grouping of category Items mainly in report matrices and/or chart matrices. In the matrices of the model for basic calculations, instead of grouping the Items of categories, I use their numerical encoding. For example, all Ctg ActualsYear years will have a value of 1, all Ctg FutureYear years will have a value of 2.

Using the TL-module in its present capacity does not give me any advantages in modeling, so I do not use it.

All the hypothetical improvements you have listed will not fundamentally change anything in modeling for me, and the lack of hypothetical improvements currently does not limit me in modeling in any way.

Good luck

Gilbert Posted new comment April 27, 2024

Dear S A U,
thank you for your valuable comments.

I think your suggestion of using a “Group-Descriptor” instead of grouping is a very important one, as it helps to overcome the shortcoming of not being able to use groups in approach 2 “Automatic categories”. (The only difference being that groups can be used in the “IN” statements, where descriptor can not).

Thanks, Gilbert


Dear S A U,

Thank you very much for your input! I like your alternative formulas to the ones I suggested. They might be somewhat easier to understand and maintain. I think it’s a valid suggestion to improve the identified downsides of approach 2.

Question: As you suggest only formulas for approach 2 (automatic Year categories), can I conclude that this is the approach you recommend and preferentially use in your models?

=> So you do not use groups? (approach 1) (Since this needs manual intervention to change timelines) – why not?

=> So you do not use Timelines (approach 3) – why not? Would you use timelines if some of their weaknesses would be corrected? What do you think about my improvement suggestions above?

Thank you for your input to the discussion. Everybody else still welcome!


Gilbert Answered question April 2, 2024

Hey Gilbert,

Please take a look at my solution to your problem. Maybe you’ll find answers to some of your questions here.


Good luck

S A U Edited answer April 2, 2024

Latest Questions