IDSB Banner

Leveraging the @ and # Operators in Formulas

There are times in a model when you want to utilize the names and positions of the items in your matrix formulas. Perhaps you have a category of Years and you want to pull the actual Year value into a formula. Or you have a category of Months and you want to translate the May item as the value of 5 in a formula. The use of @ and # operators in Quantrix formulas allows you to do just that.

In this example, we use the names and positions of items to calculate how many months away the current month is from a specified date. There are three formulas in the model to illustrate how these operators work.

Formula 1

Year = value(@YEARS)

This formula states: Take the item names contained in the 'YEARS' category and place them into the cells associated with the ‘Year’ item. The ‘@’ operator is the key for moving the item names into the cells. The ‘@Years’ is surrounded by the ‘Value’ function so it will turn the text of the year into a number.

Formula 2

Month = #MONTHS

This formula calculates the position of the Month item from category 'MONTHS' into the appropriate cell. The ‘#’ operator is the trigger for the Quantrix formula to return the position of the item as a value to the cell. So 'May' returns a value of 5, 'Nov' a value of 11 and so on.

 Formula 3

Number of Months from Date = (Specified Date::Year - value(@YEARS)) * 12 + (Specified Date::Month - #MONTHS)

This formula takes the concepts of formula #1 and formula #2 and puts them together to perform a calculation. The calculation determines how many months away each Month / Year combination is from a specified date. In this case, the specified date is in the Year 2004, Month 6. Lets look at the calculation for the month of January, year of 2003. The first part of the formula states: (Specified Date::Year - value(@YEARS)) * 12 Specified Date::Year returns a value of 2004; Value(@Years) returns the number 2003. Those two values are subtracted to get the result of 1 and then multiplied by 12 to derive the answer of 12 The second half of the formula states: (Specified Date::Month - #MONTHS) Specified Date::Month returns the value of 6 and #MONTHS returns the value of 1. Those two values are subtracted which produces the result of 5. Lastly, the 12 from the first half of the formula and the 5 from the second half of the formula are added together to produce the result of 17. So the formula calculates that January, 2003 is 17 months away from June, 2000. The years 2003 – 2010, and all the months contained therein, are thus calculated by one powerful Quantrix formula utilizing the @ and # operators.

Formula 4

First Day of Month = date(value(@YEARS),#MONTHS,1)

This formula is a simple way to take the values from the Year and Month items and turn them into a date. The Date function requires 3 arguments; Year, Month and Day. The Year and Month arguments are derived using the @ and # techniques discussed earlier in this Modeler Note and the value of 1 is used for the first day of the month. Utilizing these techniques along with the Date function allows you to easily turn every Year and Month combintation into a date using one Quantrix formula.

The techniques demonstrated in this Modeler Note are often useful when applying statistical functions to solve optimization and regression problems (see  Using Statistical Functions in Banking Model).

  >> Operators Model

About Quantrix - Contact - Site Map - Refer A Friend - Support
Solutions - Products - Services - Community - Partners - Webinars - Learning - Store
© Copyright 2014 Subx Inc.-All rights reserved -+1.207.775.0808 -