Calcuations on a Date (Year, Month, Quarter, Week)

4.41K viewsTips and Techniques
0

Hi,

I often have to calculate the or extact the
– Year
– Quarter
– Month
– Week

of a date.

The attached model gives some hints on how you can achieve this. Quantrix does not have built-in functions for caluclating the week or the quarter. I therefore did some online research to find formulas for these requests.

The formula to calculate the quarter is:
‘Quarter of Date (v2)’ = INT((MONTH(Date)-1)/3)+1

The formula to calculate the week is:
‘Week # of Year’ =TRUNC((Date – weekday(Date;2)-date(year(Date + 4 – weekday(Date;2));1;-10))/7)
(Source: [url:2cxqgvi5]http://de.openoffice.info/viewtopic.php?f=2&t=19780&p=84431[/url:2cxqgvi5] (German) )

The Quantrix function ‘weekday’ uses the optional parameter ‘2’. This means that the weekday is calculated with Monday as the starting day of the week and Sunday as the last day of the week. Just replace the ‘2’ with ‘1’ or nothing if you need the Sunday as the starting day of the week.

Having these formulas allows the user now to develop roll-ups from day to weeks, months, quarters and years.

Check the attached zip file including the YearQuarterMonthWeek.model file and a screenshot.

Enjoy!

Regards,
Dominik

0

Well, that is about where the Rolling Stones lyrics end other than I might say that Time is on The Side. As many of you are aware, time is always a good topic within Quantrix. Since almost all models have time as a dimension, I thought I would add one more technique for dealing with time.

First and foremost, I have to give credit to another source. This approach was first worked out by John Taylor of Planning Models Ltd, [url:cgkvjqpr]http://www.planningmodels.co.uk[/url:cgkvjqpr]. John has been using Quantrix for a couple of years and he and I have batted some time ideas back and forth for awhile.

John has taken the approach to put all time elements (months, quarters and year total) within a single time category. He then creates four formulas to obtain the quarterly values and another formula to get the yearly value (Formulas 5 through 9 in the Sales and Margin matrix).

What is interesting with this approach is how it appears on the presentation canvas. If you look at the sample model, you will see that John has made use of the radio buttons on the canvas to control just which time element appears. John uses the Periods Display Condition and Display Check Box matrices along with a custom filter to gain the desired result.

The other key aspect is the reference he makes across the Years category. In the sample model, John is making use of the [x] recursion technique. This approach allows for the months to be called by their month number vs. their month name (See Formula 4 in the Sales and Margin Matrix). Either approach is perfectly fine. It is really a modeler’s stylistic choice. For those unfamiliar with the use of [] recursion, [1] is the [FIRST] first item, [2] is the [FIRST +1] item, and so on. In this case, it makes sense because [12] is always December. Obviously, Period:Dec would work as well.

Thanks John for your method and I will put that in my toolbox as well.

0

Steven,

Thank you for your kind words and for sharing your model. Especially the usage of the select statement including the computation of the quarters of the financial year is interesting for me.

Your posts here are all very useful and I have copied them all into my personal Quantrix manual.

What do you think of writing a book called “Hands on Quantrix – The ultimate Guide to Financial and Business Modelling”? We could collect all interesting questions and solutions in this forum and from our experience and organize them as tutorials in the book. Further, the book should start with the Quantrix model development process and then continue with explanations on how to start with a new model from scratch. I could write some tutorials (we could use the one I have written to develop your own functions with Eclipse/Java or “How to translate an Excel spreadsheet with VBA code to a Quantrix model” etc.).

The book could be produced and published with lulu ( [url:l3wm97ug]http://www.lulu.com/[/url:l3wm97ug] ) to keep cost low. Further, you can update the book at any time without having to wait for the next print run. That’s a big advantage for this book because you can react quickly if a new version of Quantrix with new features is released. Further, you don’t have to find a publisher who is ready to publish it. The book is printed on demand only

What do you think?

All the best,
Dominik

0

Dominik, excellent formula for calculating the week of the year. You are correct that Quantrix does not have a function calculating the week or quarter.

I have attached a model that contains my approach to the problem. I use the SELECT statement to determine the appropriate quarter. I also included how to adjust for fiscal periods as well. Once the date manager has been established, I can use throughout the model plus paste it into any new models. I have never put the date manager into the default template but I could see where that could be useful.

Thanks for the post and I will put a couple of your formulas in my bag of tricks.

Latest Questions