5. Aprēķināmie rādītāji (Angļu valodā)

Katram aprēķināmajam rādītājam ir nosaukums ( unikāls dimensijas ietvaros) un aprēķinu formula ( pārējā apmācības daļa mācīs, kā rakstīt aprēķinu formulas ). Turklāt jūs varat norādīt, kā aprēķinātā rādītāja vērtība ir jāformatē ( piemēram, kā skaitlim , decimāldaļai, datumam vai izmantojot noklusējuma formatējamu ) . Labajā sānu joslā, jūs varat ātri izvēlēties citus rādītājus, dimensijas, operācijas vai funkcijas, lai ievietotu tos aprēķina formulā .

Pēc tam, kad esat definējis aprēķināmā rādītāja formulu, spiediet Atjaunot ( Update) lai saglabātu to, vai Izdzēst ( Delete), lai izdzēstu konkrēto aprēķināto rādītāju, Atcelt (Cancel), lai atceltu izmaiņas. 

 Ja aprēķināmā rādītāja formula būs sastādīta nepareizi, ekrānā parādīsies kļūdas paziņojums.
  Nemēģiniet labot Aprēķināmos rādītājus , kas ir sistēmas vai sākotnējā datu importa definēti - to oriģinālā formulas definīcija tiks atjaunota pie nākamā datu importa.  Ja jums nepieciešams labot jau eksistējoša aprēķināmā rādītāja formulu, tad nokopējiet esošo formulu, izveidojiet jaunu aprēķināmo rādītāju ar citu nosaukumu, iekopējiet jūs interesējoša rādītāja formulu un veiciet labojumus.

Ķersimies klāt pie formulu rakstīšanas!  Sekojošie formulu piemēri tiks veidoti uz Pārdošana kuba (Sales cube) dimensijām un rādītājiem.

When you use Measures.Store Sales then it will calculate Store Sales measure value for other corresponding dimension members for each row / column in report. If in your calculation formula you would like to override some other dimension value then you need to use tuples which is combination of members from different dimensions. MDX syntax for tuples is (member_1, member_2, ..., member_n). For example, if you would like to get Store Sales measure value for all customers then you would use tuple (Measures.Store Sales, Customers.DefaultMember.
Let's define calculated measure Measures.[] with formula (and percentage formatting)Measures.Store Sales / (Measures.Store Sales, Customers.DefaultMember)
When in report you will combine Measures.[] with e.g. Customers.USA.CA then you will see percentage of California sales from total customer sales.
Maybe you don't want to see percentage from total sales but percentage from customer hierarchy direct parent sales (e.g. for city level show percentage from corresponding state sales). In this case you can use CurrentMember dimension or hierarchy property to access current member of corresponding dimension for which expression currently is evaluated. E.g. Customers.CurrentMember will return current member in Customers hierarchy for which this expression is evaluated. Or in other words if Customers dimension is placed on report rows then by using Customers.CurrentMember we know for which row this formula is currently evaluated.
If we have current member then we can navigate to other dimension members relative from this member. Customers.CurrentMember.Parent will return parent member in Customers hierarchy for current member. Ancestor(Customers.CurrentMember, Customers.Country) will move from current member up to "ancestor" in Country level.
So we can define calculated measure Measures.[] asMeasures.Store Sales / (Measures.Store Sales, Customers.CurrentMember.Parent)

As you can move to different dimension hierarchy levels you can also move to different dimension members in the same dimension. It is typically used in Time dimension when you want to compare measures between different time periods or aggregate time period range.
For example, calculated measure Measures.Sales monthly growth with formulaMeasures.Store Sales - (Measures.Store Sales, Time.CurrentMember.PrevMember)
will calculate Measures.Store Sales growth comparing to previous Time dimension member period. If this calculated measure will be combined in report with month in Time dimension it will show growth comparing to previous month, if it will be combined with year then it will show growth comparing to previous year.
There are several functions that help you "move in time":

• Time.CurrentMember.PrevMember returns previous member in the same hierarchy level (it will return empty member for the first member)
• Time.CurrentMember.NextMember will return next member
• Time.CurrentMember.Lag(2) will return previous member with distance 2 (use any number for argument, Lag(1) is the same as PrevMember)
• Time.CurrentMember.Lead(2) will return next member with distance 2 (Lead(1) is the same as NextMember)
• ParallelPeriod(Time.Year, 1, Time.CurrentMember) will return "parallel" Time member one year ago (e.g. for day level member Jan 01 2012 it will be day Jan 01 2011 but for month level member Jan 2012 it will be month Jan 2011)
• OpeningPeriod(Time.Day, Time.CurrentMember) will return first descendant of current Time member at Day level (first day of year, quarter or month which is defined in Time dimension)
• ClosingPeriod(Time.Day, Time.CurrentMember) will return last descendant of current Time member at Day level (last day of year, quarter or month which is defined in Time dimension)Sometimes you would like to test if your time navigation expression is working as you expect (before using it in further calculations). Then you can use .Name member property to get member name as calculation formula result. For example, define calculated measure Measures.test opening day with formulaOpeningPeriod(Time.Day, Time.CurrentMember).Name
  and use it in report together with Time dimension members and see if you get expected result for Time dimension members at different levels. \\\\\\\\

Now you know you to navigate to individual dimension members. But quite often you would like to perform operations on set of dimension members, for example, find sum or average value of some measure over selected set of dimension members.
The easiest way how to use sets in calculation formulas is to use list of members enclosed in curly braces, e.g.{Customers.USA.CA, Customers.USA.OR, Customers.USA.WA}
{Time.2011, Time.2012}
If you would like to select set as range of sequential dimension level members then you can specify first and last member and use : between them. For example, this will create a set of dates from Jan 01 2012 to Jan 15 2012:Time.2012.Q1 2012.Jan 2012.Jan 01 2012:Time.2012.Q1 2012.Jan 2012.Jan 15 2012
Quite often you don't want to specify exact range of members but would like to get all dimension hierarchy level members. You can do it with Members method, for example, to get all months in Time dimension:Time.Month.Members
When you want to test which members will be returned by some set expression then you can use SetToStr functions to create string of concatenated member full names. For example, define calculated measure Measures.test all months with formulaSetToStr(Time.Month.Members)
There are several other useful functions for working with sets, here are some examples how to use them:

• Customers.USA.CA.Children returns set of children members using dimension hierarchy (in this example all cities in California)
• Descendants(Customers.USA, Customers.City) returns set of member descendants at specified hierarchy level (in this example all cities in USA)
• Customers.USA.CA.San Francisco.Siblings returns all members which have the same parent as this member (in this example all cities in California), it is the same as using Customers.USA.CA.San Francisco.Parent.ChildrenThere are additional methods that you can use to get just first or last members of these sets - FirstChild, LastChild, FirstSibling, LastSibling. \\\\\\\\\\\\\\\\\\\\\\\\\\

There are additional useful set selection methods for Time periods

• PeriodsToDate(Time.Year, Time.CurrentMember) returns all periods from beginning of current member in specified level (in this example from beginning of current year) until specified member.
• YTD(Time.CurrentMember) is shorter version of the same function (abbreviation from Year-to-date)
• QTD(Time.CurrentMember) returns quarter-to-date set of members
• MTD(Time.CurrentMember) returns month-to-date set of members
• WTD(Time.CurrentMember) returns week-to-date set of membersIf you have just time dimension in your cube then you can use even shorter expressions YTD(), QTD(), MTD() and WTD() as by default they will receive as argument current member of time dimension Time.CurrentMember. But sometimes you need to pass different argument to these functions. For example, if you would like to compare current year-to-date aggregates with year-to-date aggregates year ago you can use the following expression to get year-to-date set for corresponding Time dimension member a year ago:YTD(ParallelPeriod(Time.Year, 1, Time.CurrentMember))
  And if you like shorter expression you can omit current member argument here as well YTD(ParallelPeriod(Time.Year, 1)). Most of time related functions will use Time.CurrentMember as default member argument.
  If you want to get number of last periods from Time dimension then you can use LastPeriods function. For exampleLastPeriods(3, Time.CurrentMember)
  will return set with current Time member and two previous members. If you would like to get current and next two Time periods then use negative value -3 instead of 3. \\\\\\\\\\\\\\\\\\\\\\\\\\

There are several operations that you can perform on sets:

• {set1, set2, ..., setn} returns union of two or more sets
• Except(set1, set2) returns set1 members but removes any member that is in set2
• Head(set, number) returns set with first number of members from original set (if number is not specified then set from the first set member is returned)
• Tail(set, number) returns last number of members from set
• set.Item(position) returns one member from set with specified position (starting from zero). So if you would like to get first member of set you can use 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 condition expression [Customers].CurrentMember references current set member for which 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 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 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, ', ') \\\\\\\\\\\\\\\\\\\\\\\\\\

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

You can write formulas with conditions and different results based on condition evaluation 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 exampleIIF(Measures.Profit > 0, 'Profit', 'Loss')
If there are many conditions then it is easier to use CASE function, for exampleCASE
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 other CASE form can be used, for exampleCASE Time.CurrentMember.Level.Name
WHEN 'Month'
THEN Sum(LastPeriods(3), Measures.Store Sales)
WHEN 'Day'
THEN Sum(LastPeriods(90), Measures.Store Sales)
END
In IIF and CASE conditions standard comparison operators can be used (=, <, <=, <>, >, >=) as well as AND, OR and NOT operators as well as several specific operators:

• IS returns whether two objects are the same, for example, 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 set, for example Customers.CurrentMember IN Customers.USA.CA.Children \\\\\\\\\\\\\\\\\\\\\\\\\\

Sometimes you might need to convert string expression to integer, decimal or date expression (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 value converted to integer
• CDbl(value) returns value converted to double floating number type (should be used when results should be decimal)
• Standard-BI defines additional function DateParse(value) which will try to convert value to date value using different date formats (for example both 2012-01-31 and Jan 31 2012 will be converted to correct date). There is MDX standard CDate function but it supports less date types. \\\\\\\\\\\\\\