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 useful set selection methods for for Time periods periods
PeriodsToDate([Time].[Year],returns returns all periods from the beginning of the current member in the specified level (in this example from the beginning of the current year) until the specified member.[Time].CurrentMember)YTD([Time].CurrentMember)is is a shorter version of the same function (an abbreviation from Year-to-date).
Note that even though Weekly hierarchy also has Year level, this function will only work in default Time hierarchy. For weekly hierarchy use thePeriodsToDate([Time.Weekly].[Year]).QTD([Time].CurrentMember)returns returns a quarter-to-date set of membersMTD([Time].CurrentMember)returns returns a month-to-date set of membersWTD([Time.Weekly].CurrentMember)returns returns a week-to-date set of members.
As mentioned earlier If you
...
have just one Time dimension in your cube then you can use even shorter
...
expressions YTD(), QTD(), MTD()
...
and WTD()
...
as by default they will receive as an argument the current member of the time
...
dimension – [Time].
...
CurrentMember. But sometimes you need to pass a different
...
argument to these functions. For example, if you would like to compare
...
current year-to-date aggregates with year-to-date aggregates
...
a year ago then you can use the following expression to get a year-to-date set for the
...
corresponding Time
...
dimension member a year ago:
| Code Block |
|---|
YTD(ParallelPeriod([Time].[Year], 1, [Time].CurrentMember)) |
And if you like shorter
...
expression then you can omit the current member argument here as well – YTD(ParallelPeriod([Time].[Year], 1)). Most of the time related functions will
...
use [Time].CurrentMember
...
as a default member argument.
If you want to get a number of last periods from the Time
...
dimension then you can use
...
the LastPeriods
...
function. For example
...
:
| Code Block |
|---|
LastPeriods(3, [Time].CurrentMember) |
will return a set with the
...
current Time
...
member and the two
...
previous members. If you would like to get the current
...
and
...
the next
...
two Time
...
periods then use
...
negative value -
...
3 instead of 3. And you can omit the last argument for this function as well and use LastPeriods(3).
When you want to get the current Time dimension member in the Weekly hierarchy then you need to use [Time.Weekly].CurrentMember member expression. Sometimes you need to write a formula which should use either [Time].CurrentMember (when creating e.g. monthly report) or [Time.Weekly].CurrentMember (when creating weekly report). In this case you can use
| Code Block |
|---|
[Time].CurrentHierarchyMember |
Set operations
There are several operations that you can perform on sets:
...