Page Comparison

If you have not yet introduced yourself to the basic concepts of flex.bi then please start with an overview of flex.bi concepts and learn how to analyse and create reports. This tutorial will explain how to define new calculated members using MDX calculation formulas.

If you have never worked with MDX before, we suggest you watch this video: MDX basic concepts 

Dimensions and hierarchies

...

There are additional methods that you can use to get just the first or the last member of these sets – FirstChild, LastChild, FirstSibling, LastSibling. 

Sets of time periods

There are additional useful set selection methods for Time periods

...

YTD(ParallelPeriod([Time].[Year], 1, [Time].CurrentMember))

LastPeriods(3, [Time].CurrentMember)

[Time].CurrentHierarchyMember

Set operations

There are several operations that you can perform on sets:

• {set1, set2, ..., setn} returns  returns a union of two or more sets
• Except(set1, set2) returns  returns set1 members  members but removes any member members that is in are in set2
• Head(set, number) returns  returns a set with the number of members specified by the given number from first number of members from the original set (if the given if number is  is not specified then a set from the first set member from the set is returned)
• Tail(set, number) returns  returns the last last number of  of members from a set
• set.Item(position) returns  returns one member from a set with a specified the specified position (starting from zero). So if you would like to get the first member of a set you can use the expression expression Head(set).Item(0).

Quite frequently you would like to filter

...

set members using some condition. You can do this

...

with Filter(set, condition). For example, in this way you can filter all cities with sales larger than

...

1000

...

:

Filter([Customers].[City].Members, [Measures].[Store Sales] > 1000)

...

Within the condition expression [Customers].CurrentMember

...

 references the current set member for which the condition is evaluated. For example, this will return all cities

...

which name starts

...

with San (

...

using MATCHES operator with regular expression)

...

Filter([Customers].[City].Members, [Customers].CurrentMember.Name MATCHES 'San .*')

...

...

Other typical function that is used in conditions

...

is IsEmpty

...

. This expression will return all cities which have non-empty sales

...

amount:

Filter(  [Customers].[City].Members,
  NOT IsEmpty([Measures].[Store Sales])
)

...

Previously

...

a simple set to string

...

function SetToStr

...

 was mentioned

...

that is useful for expression testing purposes. But if you would like to format

...

set results in a

...

customized way then you can

...

use Generate(set, string_expression, separator_string). For example, the following expression will return city names concatenated using

...

comma where there is no sales amount recorded

...

:

Generate(  Filter(
    [Customers].[City].Members,
    IsEmpty([Measures].[Store Sales])
  ),
  [Customers].CurrentMember.Name,
  ', '
)

Aggregates

Now that you know how to select different sets of dimension members you can calculate different aggregated values from these sets:

• Sum(set, numeric_expression) calculates  calculates numeric_expression for each  for each set member  member and returns a sum of all these results. For example, Sum(LastPeriods(3). , [Measures].[Store Sales]) will  will calculate the total sales for the last three three Time periods  periods starting from current the current Time dimension  dimension member.
• Count(set) returns  returns a count of set members. Count(set, ExcludeEmpty) will  will return a count of set members for which corresponding measure values are not empty.
• Avg(set, numeric_expression) calculates  calculates the average of of numeric_expression in a  in set
• Max(set, numeric_expression) returns  returns the maximum value of of numeric_expression in a  in set
• Min(set, numeric_expression) returns  returns the minimum value of of numeric_expression in a set

Conditions

...

•  in set
• Median(set, numeric_expression) returns the median value of numeric_expression in set

Aggregate members in other dimensions

In the beginning of this tutorial we defined a [Customers].[West coast] calculated member in the Customers dimension with a formula:

[Customers].[USA].[CA] + [Customers].[USA].[OR] + [Customers].[USA].[WA]

But it would be better if we could expand or drill into West coast when using it in reports. To enable that you need to define calculated member using Aggregate(set) function. When you will combine such calculated member in a report together with other measure then you will get an aggregated result (by default a sum) of this measure over the specified set of members. So if you will define a [Customers].[West coast] calculated member with a formula

Aggregate({
  [Customers].[USA].[CA],
  [Customers].[USA].[OR],
  [Customers].[USA].[WA]
})

then you will get the same result as previously but in addition you will be able to expand or drill into West coast member and see the detailed results for CA, OR and WA.

In similar way you can aggregate more complex set expressions. For example, you could define [Customers].[US without west coast] calculated member with a formula:

Aggregate(  Except(
    [Customers].[USA].Members,
    { [Customers].[USA].[CA],
      [Customers].[USA].[OR],
      [Customers].[USA].[WA] }
  )
)

which will calculate aggregate of all US states without west coast states and you can drill into these states as well.

Conditions

You can write formulas with conditions using IIF and CASE functions. For simple IF / THEN / ELSE type of conditions use IIF(condition, if_true_expression, if_false_expression) (notice that there are two I letters in IIF), for example

IIF([Measures].[Profit] > 0, 'Profit', 'Loss')

If there are many conditions then it is easier to use CASE function, for example

CASE 
WHEN [Measures].[Profit] > 1000 
THEN 'Big profit' 
WHEN [Measures].[Profit] > 0 
THEN 'Small profit' 
ELSE 'Loss' 
END

If all conditions are comparisons of the same expression to different expected values then another CASE form can be used, for example

CASE Time.CurrentMember.Level.Name 
WHEN 'Month' 
THEN Sum(LastPeriods(3), [Measures].[Store Sales]) 
WHEN 'Day' 
THEN Sum(LastPeriods(90), [Measures].[Store Sales]) 
END

In In IIF and  and CASE conditions  conditions standard comparison operators can be used (=, <, <=, <>, >, >=) as well as as AND, OR and  and NOT operators  operators as well as several specific operators:

• IS returns whether two objects are the same, for example

  Code Block
  [Customers].CurrentMember IS [Customers].DefaultMember

    (which will be true if Customers current member is default All Customers member)

• IN and NOT IN returns whether member is in a set, for example

  Code Block
  [Customers].CurrentMember IN [Customers].[USA].[CA].Children

Member properties

Dimension members has some default properties (like like .Name and .Key) as well as they can have additional custom properties. In flex.bi the eazyBI source application import (e.g. from Basecamp, Highrise or JIRA) are also importing additional dimension fields from source systems. MDX has a standard standard Properties function  function to access member properties but flex. bi defines an improved getProperty function (eazyBI defines an additional get (and also with a longer name getProperty) function which will return an empty result instead of an exception if no property is defined for the current dimension level).

 For example, Highrise JIRA import adds the Created date property at property for all imported dealsissues. The following expression returns the Created date property at property value for Deals dimensions the Issue dimension current member - [Deals:

[Issue].CurrentMember.

...

get('Created

...

 at')

Date type conversion

Sometimes you might need to convert a string expression to an integer, a decimal or a date expression value (for example, to convert Highrise custom field string value to corresponding type to be able to use it in further calculations). There are several functions available for data type conversions:

• CInt(value) returns  returns a value converted to an integer
• CDbl(value) returns  returns a value converted to a double floating number type  type (should be used when results need to should be a decimal value)
• flex.bi eazyBI defines additional function DateParse(value) which  which will try to convert a value to a date value using different date formats (for example both both 2012-01-31 and  and Jan 31 2012 will  will be converted to a the correct date). There is an a MDX standard standard CDate function  function but it supports less date typesdate formats.
• DateParse function also supports dynamic date expressions as offset from today (learn more about them in date filters help page). For example, you can use DateParse('today') or DateParse('30 days ago') or DateParse('1 week from now').

Time difference calculations

flex.bi defines Flex.bi defines an additional function DateDiffDays(from_date, to_date) which  which will return the a difference in days between two dates. It can be used together with the with Now() function  function (which returns the current time) to get a distance in days between a selected time Time dimension member and the current date, for example

...

In addition, flex.bi defines the function DateAddDays(date, number_of_days) which will return a new date in the past (if number_of_days is negative) or in the future (if number_of_days is positive). For example, this will return a date which is 5 days from the current Time dimension member - [Date].DateAddDays(DateParse([Time].CurrentMember.Name), 5)Sometimes it is useful to get the Time dimension member which corresponds to an actual current date. This can be done with the flex.bi specific dimension hierarchy level property CurrentDateMember. For example, these expressions will return the month of the current date and the week of the current date [Time].[Month].CurrentDateMember
 [Time].[Weekly].[Week].CurrentDateMember
If there is no Time dimension level that corresponds to the current date (e.g. if data for current month or week is not yet imported) then the CurrentDateMember function will return the last period before the current date which is present in the corresponding Time dimension level.days is negative) or in the future (if number_of_days is positive). For example, this will return a date which is 5 days from the current Time dimension member

{[Date].DateAddDays(DateParse([Time].CurrentMember.Name), 5)

Sometimes it is useful to get the Time dimension member which corresponds to the actual current date. This can be done with eazyBI specific dimension hierarchy level property CurrentDateMember. For example, these expressions will return the members for the month of the current date and the week of current date:

Default values

Sometimes you might want to return some default value if a measure or function returns an empty value. In these cases you can use the CoalesceEmpty(expression, default_value) function, for example - CoalesceEmpty([Measures].[Store Sales], 0)

...