Skip to main content

Fiscal Week, Fiscal Month, Fiscal Quarter and Fiscal Year calculations in Microstrategy

Fiscal Week, Fiscal Month, Fiscal Quarter and Fiscal Year calculations in Microstrategy


FiscalWeek

Returns the numeric position of a week within a fiscal year, for a given input date. This function is useful in financial reporting when the start of the fiscal year is different than the start of the calendar year.
Syntax
FiscalWeek<firstWeekDay, firstMonth>(Date/Time)
Where:
•Date/Time is the input date or timestamp.
•firstWeekDay (default value is 1) is a parameter that determines which day of the week is considered as the first day of the week. You can type an integer value from 1 to 7, with 1 representing Sunday, 2 representing Monday, and so on until 7 representing Saturday.
•firstMonth (default value is 1) is a parameter that determines which month is considered as the start of the fiscal year. You can type an integer value from 1 to 12, with 1 representing January, 2 representing February, and so on until 12 representing December.
For information on whether your database supports various date and time functions, see MicroStrategy and Database Support for Functions.
Example
Consider a report or dashboard that includes a Day attribute with a single ID form. You can create a metric with the following definition:
FiscalWeek<firstWeekDay=1, firstMonth=7>(Day)
For each element of the Day attribute, the metric displays the numeric position of the week within the fiscal year for that date. For this example, since the fiscal year starts in July, a date of July 8, 2014 would return 2. This is because the first fiscal week runs from July 1st through July 5th. Then on July 6th, the first Sunday of the fiscal year, the second fiscal week starts. This week includes July 8th, and so 2 is returned.

FiscalMonth

Returns the numeric position of a month within a fiscal year, for a given input date. This function is useful in financial reporting when the start of the fiscal year is different than the start of the calendar year.
Syntax
FiscalMonth<firstMonth>(Date/Time)
Where:
•Date/Time is the input date or timestamp.
•firstMonth (default value is 1) is a parameter that determines which month is considered as the start of the fiscal year. You can type an integer value from 1 to 12, with 1 representing January, 2 representing February, and so on until 12 representing December.
For information on whether your database supports various date and time functions, see MicroStrategy and Database Support for Functions.
Example
Consider a report or dashboard that includes a Day attribute with a single ID form. You can create a metric with the following definition:
FiscalMonth<firstMonth=4>(Day)
For each element of the Day attribute, the metric displays the numeric position of the month within the fiscal year for that date. For this example, since the fiscal year starts in April, a date of July 4, 2014 would return 4. This is because July is the fourth month in the fiscal year.

FiscalQuarter

Returns the numeric position of a quarter within a fiscal year, for a given input date. This function is useful in financial reporting when the start of the fiscal year is different than the start of the calendar year.
Syntax
FiscalQuarter<firstMonth>(Date/Time)
Where:
•Date/Time is the input date or timestamp.
•firstMonth (default value is 1) is a parameter that determines which month is considered as the start of the fiscal year. You can type an integer value from 1 to 12, with 1 representing January, 2 representing February, and so on until 12 representing December.
For information on whether your database supports various date and time functions, see MicroStrategy and Database Support for Functions.
Example
Consider a report or dashboard that includes a Day attribute with a single ID form. You can create a metric with the following definition:
FiscalQuarter<firstMonth=7>(Day)
For each element of the Day attribute, the metric displays the numeric position of the quarter within the fiscal year for that date. For this example, since the fiscal year starts in July, a date of October 13, 2014 would return 2. This is because October is in the second quarter of the fiscal year.

FiscalYear

Returns the fiscal year of the input date. This function is useful in financial reporting when the start of the fiscal year is different than the start of the calendar year.
When determining the fiscal year, the year returned is the year in which the fiscal year ends. For example, if a fiscal year runs from March 1, 2014 through April 30, 2015, the fiscal year is 2015.
Syntax
FiscalYear<firstMonth>(Date/Time)
Where:
•Date/Time is the input date or timestamp.
•firstMonth (default value is 1) is a parameter that determines which month is considered as the start of the fiscal year. You can type an integer value from 1 to 12, with 1 representing January, 2 representing February, and so on until 12 representing December.
For information on whether your database supports various date and time functions, see MicroStrategy and Database Support for Functions.
Example
Consider a report or dashboard that includes a Day attribute with a single ID form. You can create a metric with the following definition:
FiscalYear<firstMonth=2>(Day)
For each element of the Day attribute, the metric displays the fiscal year for that date. A date of July 4, 2013 would have a fiscal year of 2014.
Labels:

Comments

Post a Comment

Popular posts from this blog

Microstrategy Dossiers explained

Microstrategy  Dossiers With the release of MicroStrategy 10.9, we’ve taken a leap forward in our dashboarding capabilities by simplifying the user experience, adding storytelling, and collaboration.MSTR has  evolved dashboards to the point that they are more than dashboards - they are  interactive, collaborative analytic stories . Ultimately, it was time to go beyond dashboards, both in concept and in name, and so  the've  renamed VI dashboards to  ‘ dossiers ’.  Dossiers can be created by using the new Desktop product or Workstation or simply from the Web interface which replaces Visual Insights. All the existing visual Insights dashboards will be converted to Dossiers   With MicroStrategy 10.9, there was an active focus on making it easier to build dashboards for the widest audience of end users. To achieve this, some key new capabilities were added that make it easier to author, read, interact and collaborate on dashboards ...
7 comments
Read more

Data Mart Reports in Microstrategy

Creating Data Mart Reports in Microstrategy   When there is requirement to store all the report results to a database table you can use the interesting feature in Microstratgey called Data Mart Reports. To create a data mart table, you first create a data mart report that defines the columns of the data mart table. You then create the data mart table and populate it with data. The steps below walk you through the process of creating a data mart report and then executing the report to create a data mart table. The steps also include an example for most steps, based on Tutorial sample data in the MicroStrategy Tutorial project.                Follow the simple steps below to create a datamart report: 1 In MicroStrategy Developer, create a new report or select an existing report to use as the data mart table. The report should contain the attributes...
1 comment
Read more

Allow a Visualization to Update the Data in Another Visualization in Dossier

Allow a Visualization to Update the Data in Another Visualization After adding multiple visualizations to a dossier, you can select values in one visualization (that is, the source) to automatically update data in another visualization (that is, the target). This is done by creating a filter on a visualization that targets other visualizations. To Add a Target Visualization to Your Dossier: Open the dossier with the visualization. Click  Insert Visualization   . A blank visualization appears in the dossier. From the Visualizations panel, select  Grid   . Drag an attribute from the Datasets panel to the  Rows  area of the Editor panel to add attributes to the rows. Drag an attribute from the Datasets panel to the  Columns  area of the Editor panel to add attributes to the columns. Drag a metric from the Datasets panel to the  Metrics  area of the Editor panel, to add a metric to the grid. The Metric Names attribute automatically appears i...
Post a Comment
Read more

Microstrategy Authentication Using the URL API

Microstrategy Authentication Using the URL API Users have to be authenticated before accessing functionality in MicroStrategy Web. Using the URL API, there are three ways for MicroStrategy Web to obtain the information needed to authenticate a user. Opening the login page to gather user for credentials    Bypassing the login page by providing credentials in the URL    Bypassing the login page by providing the session state in the URL A detailed explanation of each method for obtaining the authentication information is provided below. Opening the login page to gather user for credentials If the URL attempts to access a MicroStrategy Web page that requires login and no credentials or session state are provided in the URL, the user is redirected to the login page. If login is successful, the user is redirected to the specified page.   The sample URL shown below executes a report without providing authenticating information. Since the Repo...
Post a Comment
Read more

Fact tables levels tables in Microstrategy explained

Fact tables levels in Microstrategy: Fact tables are used to store fact data. Fact tables should contain attribute Id's and fact values which are measurable. All the descriptive information about the fact tables should stored in Dimension tables either in Star Schema fashion or Snow Flake Schema fashion which is best suited to your reporting solution. Since attributes provide context for fact values, both fact columns and attribute ID columns are included in fact tables. Facts help to link indirectly related attributes using these attribute ID columns. The attribute ID columns included in a fact table represent the level at which the facts in that table are stored. So the level of a fact table in the Fact_Item_Day_Customer can be the attribute Id's which is at Day, Item & Customer Id level. For example, fact tables containing sales and inventory data look like the tables shown in the following diagram: Base fact columns ver...
2 comments
Read more

display a group horizontally in MSTR document

Display a group horizontally in MSTR document By default,  groups are displayed vertically in a document.  This means that the detail sections are displayed below the Group Header. For example, a document is grouped by Year. The Detail section includes revenue and profit information by region.  Displaying the group vertically yields the following document: For certain documents, displaying and printing the group horizontally is desired. When displayed horizontally, the detail sections are displayed next to the Group Header, running horizontally across the page. The example given above, if displayed horizontally, shows a row containing the year, and then, for each region, the Region, Revenue, and Profit. When the document is viewed as a PDF, it displays as shown below: When being designed, the document with horizontal display looks like the following in MicroStrategy Developer: The sections within the group are turned sideways and listed horizontally...
Post a Comment
Read more

MicroStrategy URL API Parameters

MicroStrategy URL Structure The following table summarizes the root URL structure used for every request to MicroStrategy Web. Environment Main Application URL Administration URL J2EE http://webserver/MicroStrategy/servlet/mstrWeb http://webserver/MicroStrategy/servlet/mstrWebAdmin .NET http://webserver/MicroStrategy/asp/Main.aspx http://webserver/MicroStrategy/asp/Admin.aspx Every request sent to MicroStrategy Web calls a central controller. Parameters are appended to  Main.aspx  or  mstrWeb  (in a .NET and J2EE environment, respectively) to indicate to the controller how the request should be internally forwarded and handled. The following examples show a URL for accessing a MicroStrategy folder when the user does not have an existing session. The URL contains not only the parameters needed to connect to MicroStrategy Web, but also the parameters needed to log on and create a session. J2EE environment: <a href="http:...
2 comments
Read more

Prompt-in-prompt(Nested Prompts) in Microstrategy

Prompt-in-prompt(Nested Prompts) in  Microstrategy Nested prompts allows you to create one prompt based on the other and other bases on another, nested prompts allows us to prompt the highest level(Like year) to middle level(like Quarter, then to the low level(like Month). Here you can see how to  create a 3-level deep nested prompt that will prompt the user to select a year, then a quarter within that year, then a month within that quarter. Prompt-in-prompt is a feature in which the answer to one prompt is used to define another prompt. This feature is only implemented for element list prompts . The following procedure describes how to achieve this: Create the highest level filter. This is a filter which contains a prompt on an attribute element list. Create a filter on the attribute "Year." Click "prompt on attribute element list" and click "Next" through the rest of the screens to accept the default values. Do not set any additio...
3 comments
Read more

Apply or Pass-through functions in Microstrategy

Ap ply (Pass-Through) functions MSTR Apply functions provide access to functions or syntactic constructs that are not standard in MicroStrategy but are provided by various RDBMS systems.. Syntax common to Apply functions Apply Function Name   ("expression with placeholders", Arg1, Arg2, Arg3, …ArgN) where: Apply Function Name  – is a generic name used for the predefined pass-through functions described above expression with placeholders  – is the string describing the actual expression or syntax that the engine uses while generating the SQL and which is sent to the RDBMS. The placeholders are represented by #0, #1, and so on. "#" is a reserved character for MicroStrategy. Arg  – is an argument that replaces the parameter markers in the pattern. Arg1 replaces #0, Arg2 replaces #1, and so on. There are   five  pre-defined Apply functions to replace regular, predefined functions of the same type. For more details, cli...
6 comments
Read more

User request is completed. (Ran out of memory)

Unable to Run/Edit particular MicroStrategy reports ue to the following error: User request is completed. (Ran out of memory) User request is completed. (Ran out of memory) The above issue appeared in MSTR Web Universal version 10.5 We tried the below options without any luck: 1. i-server restart 2. Web server restart 3. clear document cache/dataset cache 4. Web server cache clear as below: The correct option is to increase the contract memory settings: Using the Memory Contract Manager The  MCM settings are in the Intelligence Server Configuration Editor, in the  Governing Rules: Default: Memory Settings  category. The  Enable single memory allocation governing  option lets you specify how much memory can be reserved for a single Intelligence Server operation at a time. When this option is enabled, each memory request is compared to the  Maximum single allocation size (MBytes)  setting. If the request ...
1 comment
Read more