Page tree
Skip to end of metadata
Go to start of metadata

A lot of the power in AAM comes from writing formulas to manipulate your data. This page is not an exhaustive explanation of all methods, but it does cover examples that are common when manipulating Projector data. It is written to target those with at least some programming experience. If you do not have any programming experience, do not worry! We try and provide both business context and code examples to aid you in creating your own formulas. If you have trouble understanding the system, you can work with a Projector Consultant to teach you, or have them implement your ideas.

Formulas in AAM are driven by Groovy, a superset of Java. Because we need to protect the system from things like infinite loops or otherwise expensive code, only a subset of Groovy API is supported. Of that subset, you may find that the syntax has been changed. If you already know Groovy or Java, you'll likely be able to learn this quickly. There are three main sources of documentation to learn about formulas.

  1. This page as an introduction
  2. BellaDati documentation to learn about specific formula functions and how they work
  3. Groovy documentation to learn coding syntax and conventions.

Formulas are used in two main areas of AAM. First, they are used when building your own indicators in reports. For example, a formula indicator to calculate accrued vacation. Second, they are used when importing data to transform it. For example, convert a double to an integer on import. You can also use formulas to modify values already stored in a data set, but that is rare.

The following links go directly to BellaDati's documentation. These are the pages I find most useful when writing formulas.

Permissions and Settings

Users with the role Data Set Manager can apply formulas to data as it is imported. They can also transform existing values in a data set via formula.

Users with the role Report Editor can create indicators driven by formulas.

Groovy

If you are more familiar with statically typed languages like Java or C#, you may find this introduction to Groovy useful - http://www.groovy-lang.org/documentation.html

As the AAM code editor isn't the most sophisticated of development environments, you may find it appropriate to use a web-based Groovy editor such as https://groovyconsole.appspot.com/ to play with Groovy.

Some pointers when you are first getting started ( http://www.groovy-lang.org/style-guide.html )

  • Semi-colons are optional and often not used at all
  • Return keyword is optional. The last evaluated statement is returned (which could be inside an if/else statement)
  • def keyword is of type object. Used for holding dynamically typed variables. 
    • def myString = "123"
    • def myInteger = 3
    • def myDouble = 3.0
  • Parenthesis can be omitted for top-level expressions. println("Hello") is the same as println "Hello". When used with closure, parenthesis may be omitted. For example, list.each { println "hi" } instead of list.each ( { println "hi" } )
  • Single line comment with //comment 
  • block-comment with /* comment */
  • When using strings, single quotes are a java.lang.string. Double quotes create a Gstring (Groovy string) which can be templated
    • def javaString = 'Hi there'
    • def groovyString = "Hi there" //template string supports neat tricks like "My name is $name". $name will be dynamically replaced by the variable it holds.

Full Groovy API is described here ( http://www.groovy-lang.org/api.html ). Remember that AAM only supports a subset of that API and that the syntax may be different.

When to use

There are three places in AAM where you are likely to use formulas. They are in:

  • Import templates - transform values as they are imported into a data set. Useful for massaging imported data as it enters the system to conform to your business needs.
  • Attribute/Indicator transformations - perform a one time transformation of an attribute or indicator in a data set. Useful for correcting values that are already in a data set.
  • Reports - define indicators programmatically for reports. Useful when you need to compute a value from other indicators. For example, Utilization = Hours Worked / Hours Supposed to Work.

Indicator and Attribute Access

This section shows you how to access indicators and attributes in your formulas. The method you use depends on where you are using the formula (as just discussed in the previous section). The following three code blocks explain how each area works for indicator and attribute access.

Import Template Editor
//cast data to the appropriate type before trying to manipulate it
//new columns, marked with an *, are not accessible by code until they are saved for the first time. You can access them by column number instead.
 
//attribute example
def location = value('L_LOCATION')
 
//indicator examples
def age = integerValue('M_AGE')
def util = doubleValue('M_BILLABLE_UTILIZATION')
 
//access via column number example
def column1 = value(1)  //you can access column data by its column location rather than its code
Attribute/Indicator Transformations
//Use value functions. Formula editor automatically casts to the correct type.
 
//attribute example
def location = value('L_LOCATION')
 
//integer examples
def i = value('M_INDICATOR_INTEGER')
def d = value('M_INDICATOR_DOUBLE')
Formula Indicators
//indicator example
def hours = M_PERSON_HOURS
 
//attribute example
def location = memberValue('L_LOCATION')   //attributes are accessible through the function memberValue(). The function only works if you have a drill-down for that attribute. (Why? - if you ask for the location without a drill-down, which of potentially many locations should be returned?)


Data Types

Groovy is a dynamic language and will attempt to cast variables to the appropriate type when necessary. Dynamically typed variables are declared using the def keyword. Def is actually an object in Groovy. Def is implicitly declared for all assignments, so you don't even need to type it out. But we type it out in the examples here for clarity's sake.

//using def
def age = 35
def name = "Tom"
println "$name is $number years old"
println age + 10
 
//def is an optional keyword and is used implicitly when not specified 
age2 = 35  //notice we didn't put def in. still works.
 
//you can see the class of an object by calling .class
def a = value('M_UNITS')
a.class //returns java.math.BigDecimal

Native Types

You can also explicitly declare your data types using the primitive java types. However, we typically recommend using def and letting Groovy handle the job of figuring out the correct data type. A full explanation of primitives and class-based data types are covered here - http://groovycookbook.org/basic_types/numbers/

//primitives
byte b1 = 42
short s1 = 20000
int i1 = 1000000000
long l1 = 1000000000000000000
float f1 = 3.5f
double d1 = 3.5e40
boolean = true

//objects (can be used instead of primitives). Notice they generally start with a capital letter.
Byte b2 = 42
Short s2 = 20000
Integer i2 = 1000000000
Long l2 = 1000000000000000000
Long l3 = 42L
Float f2 = 3.5f
Double d2 = 3.5e40
Double d3 = 3.5d
BigInteger bi = 30g
BigDecimal bd = 3.5g
Boolean = true

Casting

Casting between data types

//declare a string
def string = "300"

//cast string to integer
def i3 = string.toInteger()
def i4 = string as int
def i5 = string as Integer
def i6 = new Integer(string)
def i7 = Integer.parseInt(string)
def i8 - integerValue(string)

Tricky Data Types

Import Templates treat everything as strings. You'll need to specifically cast to the appropriate data type.

//cast to integer
def i = integerValue('M_INDICATOR')
 
//cast to double
def d = doubleValue('M_INDICATOR')


You'll likely use the following casts most frequently. 

Data TypeCast FunctionExampleNotes
Indicatornonedef a = M_INDICATORreturns java.math.BigDecimal
Dynamicvalue()def a = value('M_INDICATOR')

By leveraging def, we let Groovy do the heavy lifting of sorting out the data type. When using value() in the context of formula indicators, here are the data types returned.

value('M_PERSON_HOURS') .class == java.lang.Double
value('L_ENGAGEMENT_PRIMARY_COS').class  == class java.lang.String 
value('L_MONTH') .class == org.joda.time.Partial 

When working in the context of import templates, value always returns a string. Make sure you use one of the other casting methods if you need to perform things like mathematical operations.

value('M_PERSON_HOURS') .class == class java.lang.String 
value('L_ENGAGEMENT_PRIMARY_COS').class  == class java.lang.String 
value('L_MONTH') .class == class java.lang.String 

IntegerintegerValue()def i = integerValue('M_INDICATOR_INTEGER')
DoubledoubleValue()def d = doubleValue('M_INDICATOR_DOUBLE')
StringtoString()

Integer i = 1000
String s = i.toString()

Only works on object data types, not primitives. (i.e. - def and Integer work. int does not)
LocalDatedateValue()def d = dateValue()LocalDate is a class that only exists within AAM. You cannot use Groovy date objects.
LocalTimetimeValue()def t = timeValue()LocalTime is a class that only exists within AAM. You cannot use Groovy time objects.



Conditions and Loops

Because of the danger of client code triggering infinite loops or other performance degrading code, there is a very limited number of methods that you can use in AAM. This section details the available methods. Loops like while and for are not allowed.

There are a few functions which allow you to simulate loops and are protected from causing performance issues. Please see membersSum and forEachRow further down this page.

MethodExampleNotes
If / Else
def val = value('M_HOURS') as double
if (val > 100) {
    result = "Too high"
} else {
    result = "Success"
}
return result;

Ternary
def val = value('M_HOURS') as double
return val > 100 ? "Too high" : "Too low"

Switch

Notice the power of Groovy's switch statement. You can check for values in an array, within a number range, etc.

def x = '5' as string
switch (x) {
    case "Specific string value":
         result = "Contains specified string value"
         break
    case [4, 5, 'a', 'b']:
         result = "Is 4, 5, a, or b."
         break
    case 12..30:
         result = "In range"
         break
    case Number:
         result = "Is number"
         break
    default:
         result = "Default"
}


Math Functions

A full list is available here - http://support.belladati.com/doc/Math+Functions

Some examples and commonly used options are in this table.

Math FunctionExampleNotes
MaxMax(1,2) = 2Return the larger of two numbers.
MinMin(1,2) = 1Return the smaller of two numbers.
CeilCeil(3.5) = 4Round up to nearest integer.
FloorFloor(3.5) = 3Round down to nearest integer.
RoundRound(1.3) = 1Round to nearest integer
AbsAbs(-3) = 3Return absolute value

Methods

When accessing attributes or indicators from a method, you must use their unique codes. Codes can be encased in either single or double quotes. This guide uses single quotes because double quoted strings are actually Gstrings and have some special features that could unintentionally be invoked.

firstValue()

Returns the first alphabetically, lowest number, earliest date, or earliest time - depending on the type of attribute/indicator you are looking at. If no data or non-existent attribute code, then blank string is returned. 

For example, if you had a hundred rows of data that all contained dates, return the earliest date.

Often used for retrieving the Actuals Cut Off Date (ACOD) in our formulas.

def date = firstValue('L_ATTRIBUTE_CODE')
def time = firstValue('L_ATTRIBUTE_CODE')
def String = firstValue('L_ATTRIBUTE_CODE')
def BigDecimal = firstValue('M_INDICATOR_CODE')


lastValue()

Returns the last alphabetically, highest number, latest date, or latest time - depending on the type of attribute/indicator you are looking at. If no data or non-existent attribute code, then blank string is returned. 

For example, if you had a hundred rows of data that all contained dates, return the latest date.

Often used for retrieving the Actuals Cut Off Date (ACOD) in our formulas.

def date = lastValue('L_ATTRIBUTE_CODE')
def time = lastValue('L_ATTRIBUTE_CODE')
def String = lastValue('L_ATTRIBUTE_CODE')
def BigDecimal = lastValue('M_INDICATOR_CODE')


members()

Limit the drill down members based on your parameters. You can comma separate a list of items. 

//find revenue for contractors in north america and europe
members('[L_RESOURCE_TYPE=Contractor][L_LOCATION=North America,Europe]', {M_PERSON_HOURS * M_RATE} ) 


filter()

Filters are used to extract rows of data that fit your needed criteria. For example, if a data set contains rows of data for both projected and actual data, you can limit the results to just one type. You can also limit based on indicator values. For example, only show me negative cost cards.  Filters can also be nested to further refine the information shown.

//filter based on attribute values. Returns aggregated revenue across a group of cost centers
filter("L_COST_CENTER in ('Paris','Berlin','London')") {
    return M_REVENUE
}
 
//filter based on indicator values. Returns rows where more than 100k of revenue was earned.
filter('M_REVENUE > 100000') { L_COST_CENTER_COUNT }
 
//nest filters to return actual and chargeable hours
filter("L_DATA_TYPE in ('Actual')") { 
	filter("L_TIME_CARD_CHARGEABLE_Y in ('Yes')") {       
		return value('M_PERSON_HOURS')
    }
}


//the previous could also be written this way
filter("L_DATA_TYPE in ('Actual') AND L_TIME_CARD_CHARGEABLE_Y in ('Yes')") {       
		return value('M_PERSON_HOURS')
}
 
//if a filter excludes all rows from the result set, rather than returning zero, it returns a null. You should check for this and convert to 0 where appropriate
filter("L_ENGAGEMENT_TYPE in ('xxxxxxxxx')") {
      tmbudget = M_BUDGETED_RESOURCE_DIRE;
}
 
if (tmbudget == Double.NaN) { tmbudget = 0 }
 

For full documentation on filters please see http://support.belladati.com/doc/Filtering+in+Formulas.


forEachRow()

When performing mathematical operations on indicators, it may be necessary to perform that math on each row as opposed to the aggregated row data. The easiest way to imagine this is to think of a table. If you have a data set with Rate and Hours, you want to calculate revenue earned. Well that's simple. Use the formula:

Revenue = M_HOURS * M_RATE

However, if you did this, your "answer" might be wrong. See the table below.

ResourceHoursRateRevenue
Greg2100200
Greg450200
Totals6150900

Notice how you end up with 900 of revenue. That is because the multiply operation occurred on the aggregated row data. If you instead used this formula

//calculate revenue earned at the row basis
revenue = forEachRow( 'M_HOURS * M_RATE', 'SUM')

In this case you get the correct answer - $200 in revenue. 

You'll find forEachRow useful when you have multiple rows that must be combined into a single virtual row of data. 


membersSum() membersMin() membersMax() membersAvg() eachMember()

Unfortunately these formulas can introduce large performance problems. We recommend that they not be used if possible. Try ForEachRow(), FirstValue(), or LastValue() as alternatives. In the event those functions cannot fit your needs, then consider these functions. The members() functions loop over all rows in a data set, performing an operation as they go. The loop is limited to 5000 operations, so large data sets won't work with membersSum. The data is simply truncated with no warning. See AAM and Performance for more.


count += membersSum( M_UNITS )  //sum units across all rows



memberIdentifier()

Most useful when using CrossValue. Return's the attribute code of the current drill down.

//current drill down is cost center
memberIdentifier()   //[L_COST_CENTER='Europe']


//if there are multiple drill-downs on a table then memberIdentifier will return all of them
memberIdentifier()   //[L_ENGAGEMENT_CODE = 'P00001' AND L_PROJECT_CODE='P00001-001']
 
//use with cross value to pass current drill down along
crossValue('GINSU.GINSU', memberIdentifier(), 'M_REVENUE')   //revenue in the Ginsu data set, limited by current drill down


//in the event that you need to filter on just a single drill-down, see crossValue section of this help instead


memberValue()

Used to get an attribute's current value. Can only be used if you are actually drilling down on that attribute. For example, look at the table below which has three drill-down attributes specified.

Engagement CodeProject CodeProject ManagerBudget
P001001P001001-001Jim1000

P001001-002Sally2000
//get deepest drill down's attribute value
def a = memberValue()    //returns Project Manager
 
//get drill-down from specific attribute
def a = memberValue('L_PROJECT_CODE')     //returns project code
 
//get drill-down based on relative depth
def a = aggregatePrevLevel(2){ memberValue() }   //returns engagement code (notice the integer 2. So we go two levels up from deepest drill down)


aggregatePrevLevel()

Go up N steps in the drill-down tree and aggregate an indicator there. Useful when you need to know the amount of an indicator relative to a higher level drill down. For example, Tom worked 20 hours. All resources worked 100 hours. Tom contributed 20% of all hours worked.

Cost CenterResourceHoursAggregatePrevLevel(1)
MainTom20100

Greg80100
//go up one level in the drill down tree
aggregatePrevLevel(1) { M_PERSON_HOURS }
 
//calculate percentage relative to parent drill down
M_PERSON_HOURS / aggregatePrevLevel(1) { M_PERSON_HOURS }


rank()

Return the current row's rank in context of its drill down. 

//find rank
def i = rank( M_PERSON_HOURS )    //for the person that worked the fifth most hours, you'd get "5" as the result


value()

Value() extracts the current row's value and casts it to the most appropriate data type. It should really only be used when transforming attribute/indicator values. Which basically means - almost never.

//value is smart enough to convert to the appropriate data type
Integer a = value('M_INTEGER')
BigDecimal a = value('M_DOUBLE')
String a = value('L_LOCATION')
def a = value('L_DATE')  //remember that date object isn't accessible in AAM, so use generic def instead
def a = value('L_TIME')  //remember that date object isn't accessible in AAM, so use generic def instead
String a = value()       //get the current attribute/indicator's value. Codes only need to be passed in if you are accessing another members value.

The only other place value() is of use is in the import template editor. Here, it always returns an attribute/indicator as a string. Unless you are extracting strings, you'll likely want to use one of its sister methods to extract the value and cast to the appropriate type. 

  • integerValue()
  • doubleValue()
  • dateValue()
  • timeValue()

If you wanted to you could also forgo the above methods and cast values using typical Groovy.

 //there are several ways to cast variables
Integer i = integerValue()
Integer i = value() as Integer
 
//remember that date object is unique to AAM
def d = dateValue()
def d = date(value())


Dates

Date manipulation is used so often in AAM formulas that I've broken date methods into their own section. Here you will learn how to manipulate dates, limit data shown based on dates, and aggregate data between dates.

AAM uses the Groovy partial class to handle dates and time - http://joda-time.sourceforge.net/apidocs/org/joda/time/Partial.html

Just use def when working with dates. If for some reason you need to declare an actual date object, you have to use the full class path.

//create dates this way. 
//actualDate() gets today's date as a string. date() turns a string into a date object. 
def myDate = date(actualDate())
 
//but if you really want to, you can declare a date object this way
org.joda.time.Partial myDate = date(actualDate()) 

For the purposes of this documentation, we'll be referencing these date object types. 

  • LocalDate - just the date portion
  • LocalTime - just the time portion
  • LocalDateTime - both date and time
  • Partial - returned by firstValue and lastValue (documentation on partials is incomplete. need to flesh this out.)


Dates vs Strings

 When you are reviewing the following date methods, make sure you understand the object type they are expecting as inputs or produce as outputs. Does the method produce a string? Does it produce a date?

A full explanation of AAM dates can be found at this link - http://support.belladati.com/techdoc/Date+and+time

Date Formats

By default, all dates in AAM are assumed to be in the format M/dd/yyyy or 3/01/2015.

actualDate() // returns 3/02/2015 for March 2, 2015.
 
//Month does not have a preceding zero
//Day does have a preceding zero
//Four digit year
//Fore-slashes as separators

Time is assumed to be in the format H:mm:ss a or 3:01:02 PM.

actualTime() // returns 3:01:02 PM for 3 hours, 1 minute, 2 seconds in the afternoon.
 
//Hour does not have a preceding zero
//Minute and second do have a preceding zero
//AM/PM is used instead of a 24 hour clock

A domain administrator can change the format for both dates and time from Manage Domain | Format Settings link. If you change the format, formulas may need to be updated.


date()

Convert a string to a LocalDate object. You can also perform date math within the string input to change the date. Modifiers are:

  • y  - year
  • q - quarter
  • m - month
  • w - week
  • d - day
String d = '1/22/2015'                       //get a specific date
def myDate = date(d)                         
 
String today = actualDate()                  //get today's date
def myDate = date(today)
 
def myDate = date('actualYear() - 1y')       //get the first day of the current year
def myDate = date('actualYear() - 1y - 1d')  //get the last day of the previous year
def myDate = date('actualDate() - 3w')       //three weeks ago from today


time()

Convert a string to a LocalTime object

String t = '3:15:01 PM'
def myTime = time(t)
 
String now = actualTime()
def myTime = time(now)


datetime()

Convert a string to a LocalDateTime object

 String dt = '3/02/2015 3:15:01 PM'


toString()

Convert a LocalDate, LocalTime, or LocalDateTime object to a string.

def d = date('1/22/2015')
String s = toString(d, 'MMM dd, yyyy') // Mar 22, 2015


toString() Java

If you use a crossValue() to get a date object, it returns a java date object. None of the functions here work with java date objects. So, you need to cast it to a string, then back into an LocalDate object. Here is how to do that.

def resourceStartDate = crossValue('AAM__PTO_INPUT_2018',  '[L_RESOURCE_DISPLAY_NAME={' + resource +'}]', 'L_START_DATE_FIRSTVALUE')
resourceStartDate = resourceStartDate.toString()  //cast to string
resourceStartDate = date(resourceStartDate) //cast to LocalDate


datetimePart()

Get the part of a date or time that interests you.

String s = datetimePart('2018-04-29', 'dayOfWeek') //returns Friday (must use date format yyyy-MM-dd)


actualDate()

ActualDate returns today's date as a string. You can control the format by passing in a format string. There are a number of variations of this method, but they all work the same.

  • actualDay() - today's date
  • actualWeek() - date of Monday in the past
  • actualMonth() - first day of the current month
  • actualQuarter() - first day of the current quarter 
  • actualYear() - first day of the current year
//get today's date
String today = actualDate()
 
//get today's date and format it
String today = actualDate('yyyy-MM-dd') 
 
//get current date and turn it into a LocalDate object
def date = date(actualDate())
 
//pretend today's date is 7/23/2015. You can call the following methods to get the start date of a day, week, month, quarter, or year.
String today = actualDay()      // 7/23/2015
String monday = actualWeek()    // 7/20/2015
String month = actualMonth()    // 7/1/2015
String month = actualQuarter()  // 7/1/2015
String month = actualYear()     // 1/1/2015


actualTime()

ActualTime returns the current time as a string. You can optionally format that time by passing in a format string.

//get current time
String now = actualTime() 

//get current time and format it
String now = actualTime('HH:mm:ss')
 
//get current time and turn it into a LocalTime object
def now = time(actualTime())


plus()

Given a date, add time to it

//plus(localdate/localtime/localdatetime, string, integer)  -- string is the component of the datetime you want to change -- days, weeks, minutes, seconds, months, years, etc.
 
//get me next week
def today = actualDate()           //returns string
def dToday = date(today)           //convert to a date object
return plus(dToday, 'weeks', 1)   //take away a week


minus()

Given a date, subtract time from it

//minus(localdate/localtime/localdatetime, string, integer)  -- string is the component of the datetime you want to change -- days, weeks, minutes, seconds, months, years, etc.
 
//get me last week
def today = actualDate()           //returns string
def dToday = date(today)           //convert to a date object
return minus(dToday, 'weeks', 1)   //take away a week


prev()

Look at a previous date period. Useful when comparing past performance to current performance. For example, show me revenue last month vs. revenue this month. 

Inputs:

  • Day
  • Week
  • Month
  • Year
double revenue = M_REVENUE
double lastMonthRevenue = prev('MONTH', { M_REVENUE })

double revenueGrowth = abs(revenue - lastMonthRevenue) / revenue * 100


next()

Look at a coming date period. Useful when comparing future performance to current performance. For example, show me how sales are increasing or decreasing in the future.

Inputs:

  • Day
  • Week
  • Month
  • Year
double revenue = M_REVENUE
double nextMonthRevenue = next('MONTH', { M_REVENUE })

double revenueGrowth = abs(revenue - nextMonthRevenue) / revenue * 100


dateAt()

Get an indicator's value at a particular point in time. That time period can be a single day or a longer time period.

dateAt('1/01/2015', { M_REVENUE }) //how much revenue is associated with this date
 
dateAt('1/01/2015', 'MONTH', { M_REVENUE })  //how much revenue was earned in the month 1/01/2015


dateAttribute()

Allows you to specify which date attribute should be used for calculating an indicator. Mostly useful for KPIs where you need to calculate a value based on a date range. Why is it useful in such a specific case? KPIs only allow you to filter all of the underlying data based on a date range. If you want to show two KPIs next to each other, each based on different date ranges, then you can't do that by default in a KPI. By leveraging dateAttribute() you can though. Because it is used for date ranges, you' see it almost always used in conjunction with dateInterval().

dateAttribute('L_MONTH') { M_REVENUE }                               //this doesn't really do anything. Gets all revenue associated with the attribute L_MONTH. Which is really just the same as calling def a = M_REVENUE
 
dateAttribute('L_MONTH') { dateInterval(start, end) { M_REVENUE } } //but when combined with date interval, we can get revenue between two dates. Now you can show nice KPIs like "all revenue to date" next to "revenue last month"


dateInterval()

Aggregate data between two dates. Often used in conjunction with dateAttribute() when building KPIs. DateInterval is useful for retrieving values between two dates that break the current views context. For example, say you have a view for the current year, broken down by month. When accessing indicator values you'll see the data limited to the current month. With DateInterval() you can broaden that date range to whatever you want. Also see prev() and next() which are useful for getting adjacent buckets of data. For instance, to compare last month's revenue to this month's revenue.

//specify dates and return values between them
dateInterval('1/01/2015', '1/15/2015', M_REVENUE)
 
//dynamically calculate dates and return values between them
startDate = 'actualMonth() - 12m'
endDate = 'actualMonth() + 2m - 1d'
dateInterval(startDate, endDate, { M_REVENUE} ) 


withoutDateTime()

Strips the entire date context when calculating an indicator. For example, your view shows the current year broken down by month. The data set that drives it contains five years of data. When using this function, it will return the five years of data.

M_PERSON_HOURS //get hours for the current view's context. If view was for year broken down by month, returns the hours for the current month
 
withoutDateTime() { M_PERSON_HOURS } //get hours for the entire dataset regardless of view's time interval or the view's current context



cumulateFromDate()

Starting at a particular date, cumulate all preceding records. Useful when you want to see how things are increasing over time. In the example below the green line is month to month revenue. The black line is that revenue accumulating through the course of the year.

//sum revenue for the last year
def start = date(actualDate())  //get today's date and turn it into a date object
def lastYear = minus(start, 'years', 1) //subtract one year
String sLastYear = toString(lastYear, 'M/dd/yyyy')
 
def revenue = cumulateFromDate(sLastYear, 'M_REVENUE')  


cumulateFromTime()

Similar to cumulateFromDate(), but accepts a timestamp instead of a date.

def revenue = cumulateFromTime('12:00:00', 'M_REVENUE')  


viewDateFrom() / viewDateTo()

If you have a chart that is limiting displayed data via a date interval, then you may want to know the start and end dates for your formulas. These functions give access to that data.

startDate = viewDateFrom()   //returns a string
endDate = viewDateTo()		 //return a string
 
startDate = date(viewDateFrom())  //cast to date
endDate = date(viewDateTo())	  //cast to date
 



viewTimeFrom() / viewTimeTo()

If you have a chart that is limiting displayed data via a time interval, then you may want to know the start and end times for your formulas. These functions give access to that data.

startTime = viewTimeFrom()   //returns a string
endTime = viewTimeTo()		 //return a string

startTime = date(viewTimeFrom())  //cast to time
endTime = date(viewTimeTo())	  //cast to time
 


Get Parts of Date

You can retrieve the individual pieces of a date or time object. For example, the year 

Integer i = year('1/01/2015')      //2015
Integer i = month('3/01/2015')     //3
Integer i = week('12/31/2015')     //52 (last week of year)
Integer i = dayOfWeek('1/02/2015') //Monday == 1, Tuesday ==2
Integer i = dayOfYear('2/3/2015')  //34th day of year


Compare Dates

These methods allow you to compare dates and get the time difference between dates. These functions require that you pass in date objects.

def start = date('1/01/2015');
def end   = date('12/31/2015');
 
boolean b = isAfter(end, start)   //true, december 2015 is after january 2015
boolean b = isBefore(end, start)  //false, december 2015 is not before january 2015
boolean b = isEqual(start, start) //true, same date

Get time differentials.

def start = date('1/01/2015');
def end   = date('12/31/2016');
 
Integer i = daysBetween(start, end)    //730 days
Integer i = weeksBetween(start, end)   //104 weeks
Integer i = monthsBetween(start, end)  //24 months
Integer i = yearsBetween(start, end)   //1 year


Context Date and Time

If your view contains a Time and Formulas component, then you can return information in context of that component. For example, my view shows me data broken down by month. Get me the year this month is in. 

This feature only works when using the Time and Formulas feature of views. It does not work for date attributes that are added as drill down paths. If that sounds confusing, then perhaps this screenshot will help. This is what you have to be using.

Methods only work at the deepest time interval. In other words, if you call contextDay(), but the view is showing Months, then contextWeek() returns based on the first day of the month.

Imagine we are looking at a view and it is broken down to the day level - 01/02/2015
 
String s = contextDay()      //the actual date 1/02/2015
String s = contextWeek()     //first Monday of the week 12/29/2014
String s = contextMonth()    //first day of the month 1/01/2015
String s = contextQuarter()  //current quarter's first day 01/01/2015
String s = contextYear()     //jan 1 of current year 01/01/2015




Attribute Count and Distinct Count

You can calculate metrics about your attributes. For example, how many projects are in the Opportunity stage?  Just append _COUNT or _DCOUNT to get the number, or distinct number of records.

 Integer opportunities = L_PROJECT_STAGE_COUNT


Indicator Average, Min, and Max

Not only can you access an indicator via its formula name, you can also access the sum, average, min, max, and distinct count using the @ symbol. The calculation of those values in done within the context of the view. That is to say, if your view is broken down by resource, then requesting M_PERSON_HOURS@AVG would return the average number of hours a single resource worked.

def a = M_PERSON_HOURS@SUM 
def a = M_PERSON_HOURS@AVG
def a = M_PERSON_HOURS@MIN
def a = M_PERSON_HOURS@MAX
def a = M_PERSON_HOURS@DC

You can make similar calls across time intervals

def a = M_PERSON_HOURS@SUMT 
def a = M_PERSON_HOURS@AVGT
def a = M_PERSON_HOURS@MINT
def a = M_PERSON_HOURS@MAXT
def a = M_PERSON_HOURS@DCT


Report Filters

Reports can optionally have all views filtered by an attribute. For example, even though the underlying data set contains information from all cost centers, all views on this report should only show information about a specific cost center. You can access these report filters using the @ sign. First, visit the filters for your report. Then note down the filter name. Finally, prepend the filter with @ and use closure to access an indicator.

def a = @CostCenterFilter { M_PERSON_HOURS }
Variable type
Return type
Date
LocalDate
Filter
Array of Strings
Number???
StringString


Cross Reference Data Sets

You may find that you need to reference data from another data set. Typically we see this done by performing a join between two data sets. The report is then built off of the resulting joined data. However, you can also use a feature called CrossValue() that skips the joined data set. In order to cross reference, you'll need the target data set's unique code and the attribute/indicator you are looking up. The data set code can be found under the Cross-Reference dropdown in the formula editor, or by visiting the data set and looking under the More menu. 

  


CrossValue is often used in conjunction with memberIdentifier() which returns the current drill down attribute code(s). This is useful if you want to filter the CrossValue call using the currently applied drill downs.

//get person hours from the Ginsu report
crossValue('GINSUBYMONTH.GINSUBYMONTH', 'M_PERSON_HOURS')          
 
//get distinct count of resources 
crossValue('GINSUBYMONTH.GINSUBYMONTH', 'L_RESOURCE_DCOUNT')       
 
//get distinct count of resources for a specific cost center
crossValue('GINSUBYMONTH.GINSUBYMONTH', '[L_COST_CENTER={USA}]', 'L_RESOURCE_DCOUNT')    
 
//get distinct count of resources for a specific cost center and date range
crossValue('GINSUBYMONTH.GINSUBYMONTH', '[L_COST_CENTER={USA} AND L_MONTH > {01/01/2015}]', 'L_RESOURCE_DCOUNT')    
 
//get distinct count of resources for your current drill-down
crossValue('GINSUBYMONTH.GINSUBYMONTH', memberIdentifier(), 'L_RESOURCE_DCOUNT')        



//in the event that the source data set and target data set have different attribute codes, you can use memberValue() instead of memberIdentifier(), hard-coding the attribute value
//in this case I hard coded the attribute value to L_COST_CENTER2 (the source was just L_COST_CENTER)
crossValue('GINSUBYMONTH.GINSUBYMONTH', '[L_COST_CENTER2="' + memberValue() + '"]', 'L_RESOURCE_DCOUNT')

//you can specify an attribute in memberValue() if you want to get a specific drill down value
crossValue('GINSUBYMONTH.GINSUBYMONTH', '[L_COST_CENTER="' + memberValue('L_COST_CENTER') + '"]', 'L_RESOURCE_DCOUNT')


//if your crossValue returns a null you'll need to check for that, otherwise operations using it will return a null value too
def barBudget = crossValue('AAMPROJECTPORTFOLIOPERFOR','M_BUDGETED_BILLING_ADJUS')
if (barBudget != null) { return barBudget } else { return 'No Budget'}
//get attribute with FIRST or LAST value
crossValue('GINSUBYMONT.GINSUBYMONTH',  '[L_RESOURCE_DISPLAY_NAME={' + resource +'}]', 'L_START_DATE_FIRSTVALUE') 

In Practice

Here are some common charts you might want to build and the indicators formulas we used to build them.

Revenue Targets

The screenshot below shows a bunch of circles as compared to a gray bar. The gray bar is your target. When the circle is above the gray bar, you exceeded your target and we color it blue. When below, you missed your target and we color it red. 


Gray bar - Use CrossValue to get the target for a given month

crossValue('BDTARGETS.BDTARGETS', 'M_REVENUETARGET')  //


Solid red dots - actuals that missed target

//Get our target for the current month
target = crossValue('BDTARGETS.BDTARGETS', 'L_MONTH', 'M_REVENUETARGET')
 
//Filter to just actual data
filter("L_DATA_TYPE in ('Actual')"){
 
  //calculate total revenue
  totalRevenue = M_SYSTEM_REVENUE + M_ODC_REVENUE
 
  //see if we beat our target
  if (totalRevenue < target) {
      return totalRevenue                         //if we missed the target, return the revenue we did earn
  }
  else {
  	  return null                                  //if we exceeded the target, don't show anything
  }
}


KPIs

When using KPIs, you'll often want to show a subset of data based on dates. In the screenshot below, I show 2014 and 2015 revenue.

//Revenue 2014
def start =  date('actualYear() - 1y')                       //Jan 1 of last year
def end =  date('actualYear() - 1d')                         //Dec 31 of last year


//only look at actuals
filter("L_DATA_TYPE in ('Actual')") { 
 
    //based on months in last year, get me total revenue
	dateAttribute('L_MONTH') {
		dateInterval(start, end) { M_SYSTEM_REVENUE + M_ODC_REVENUE }
	}
}
 
//Revenue 2015
def start =  date('actualYear()')                       //Jan 1 of this year
def end =  date('actualYear() +1y - 1d')                //Dec 31 of this year

//only look at actuals
filter("L_DATA_TYPE in ('Actual')") { 

    //based on months in this year, get me total revenue
	dateAttribute('L_MONTH') {
		dateInterval(start, end) { M_SYSTEM_REVENUE + M_ODC_REVENUE }
	}
}


Use Actuals Through Date

In many data sets you'll have a use actuals through date. If you need to retrieve that date, you can do so with the following code. Keep in mind that we are making the assumption here that every row in the data set has the same actuals cut off date. If the dates vary row by row, you'll need to compensate.

//get actuals cut off date
def acod = firstValue('L_ACTUALS_CUTOFF_DATE')
 
//when you call actualDate, it returns a *string*. You'll need to convert it to a date data type before you can start manipulating it.
def today = actualDate() //returns string
def dToday = date(today) //convert to a date
return minus(dToday, 'weeks', 1) //take away a week

  • No labels