Some useful functions to use in metrics include
All of the above except count and first/last are non-group functions.
In rank functions, you specify the metric to be ranked and whether to rank in ascending or descending order. You can also specify whether to break by an attribute.
The level of the rank depends on the level in the report. For example, the following metric combined with customer on a report shows the highest revenue customer as number one.Rank (Revenue) <Ascending+False>
Adding a parameter to break by year displays the customer generating the highest revenue in each year as number one.
The count function is usually used with an attribute, although a fact can also be counted. You can specify whether to count from a fact or a lookup table and whether to count distinct occurrences of the target.
For example, a metric can count the number of customers in a specified region, based on the lookup table. Another metric calculates the number of customers who generated revenue in a particular month. This calculation is based on a fact table containing the revenue fact.
Running and moving sums and averages
These functions include
All these functions are similar and are called OLAP functions in the Metric Editor. The running sum function uses a metric as input and calculates a cumulative total of values based on a sort order specified in the metric definition. The sort order is not based on the report level. For example, a report with dates, revenue, and month-to-date revenue is needed. The month-to-date revenue is defined asRunningSum(Revenue) <Sort Ascending by Date>.
For input, the moving sum and average require a metric and a window size, that is, a date range.
The N-tile function, which is also referred to as segmentation, sets up numbers of groups, or tiles, for a metric. Examples of required parameters are the number of groups and whether they should be sorted in ascending or descending order.
An example of an N-tile function in use is displaying what items are in the top 25% of sales, the next 25%, and so on. Use the N-tile function with the Revenue metric. Because the results are in quartiles (four groups of 25 each), the number of tiles is four.
First and Last
The First and Last functions provide the ability to use sort-by inside aggregation functions, that is, functions where the value depends on the sort order. First returns the First value in a sorted set of values, while Last returns the last value. You can define the sort attributes in the function parameters.
For example, an inventory report lists the on-hand supply for each day. The report subtotals are the last day’s inventory. Creating a user-defined subtotal that uses the Last function provides the needed last day inventory subtotal. If the sort parameters of the function are not set to sort by Day, the function may not provide the correct answer.
For a sample scenario using the First function, see User-defined subtotal example (First function) in Chapter 2, Reports. For more details on the functions themselves, see the MicroStrategy Analytical Engine Functions Reference.
Microstrategy Related Interview Questions
|Informatica Interview Questions||IBM Cognos Interview Questions|
|Teradata Interview Questions||Adv Java Interview Questions|
|Cloud Computing Interview Questions||SQL Database Interview Questions|
|OBIEE Interview Questions||Qlik View Interview Questions|
|Tableau Interview Questions||Talend Interview Questions|
|IBM Cognos TM1 Interview Questions||Data modeling Interview Questions|
|Cognos ReportNet (CRN) Interview Questions||Qlik Sense Interview Questions|
Introduction To Advanced Reporting
Creating Freeform Sql Reports
Creating Olap Cube Reports
Data Mining Services
Custom Groups And Consolidations
All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd
Wisdomjobs.com is one of the best job search sites in India.