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 Caches explained

Microstrategy Caches Improving Response Time: Caching A  cache is a result set that is stored on a system to improve response time in future requests.  With caching, users can retrieve results from Intelligence Server rather than re-executing queries against a database. To delete all object caches for a project 1 In Developer, log into a project. You must log in with a user account that has administrative privileges. 2 From the  Administration  menu, point to  Projects , and then select  Project Configuration . The Project Configuration Editor opens. 3 Expand  Caching , expand  Auxiliary Caches , then select  Objects . To delete all configuration object caches for a server 1 Log in to the project source. 2 From the  Administration  menu in Developer, point to  Server , and then select  Purge Server Object Caches . 4 Click  Purge Now . To purge web cache follow the steps in the link ...
3 comments
Read more

Microstrategy Custom number formatting symbols

Custom number formatting symbols If none of the built-in number formats meet your needs, you can create your own custom format in the Number tab of the Format Cells dialog box. Select  Custom  as the Category and create the format using the number format symbols listed in the table below. Each custom format can have up to four optional sections, one each for: Positive numbers Negative numbers Zeros Text Each section is optional. Separate the sections by semicolons, as shown in the example below: #,###;(#,###);0;"Error: Entry must be numeric" For more examples, see  Custom number formatting examples . To jump to a section of the formatting symbol table, click one of the following: Numeric symbols Character/text symbols Date and time symbols Text color symbols Currency symbols Conditional symbols Numeric symbols For details on how numeric symbols apply to the Big Decimal data type, refer to the  Project Design Guide . ...
3 comments
Read more

Case functions Microstrategy

Ca se functions Microstrategy Case functions return specified data in a SQL query based on the evaluation of user-defined conditions. In general, a user specifies a list of conditions and corresponding return values. Case This function evaluates multiple expressions until a condition is determined to be true, then returns a corresponding value. If all conditions are false, a default value is returned.  Case  can be used for categorizing data based on multiple conditions. This is a single-value function. Syntax Case ( Condition1 ,  ReturnValue1 ,  Condition2 , ReturnValue2 ,...,  DefaultValue ) Example Case(([Total Revenue] < 300000), 0, ([Total Revenue] < 600000), 1, 2) sum(Case (Day@DESC in (“Sat”,”Sun”), Sales, 0) {~+} Sum(Case(Category@DESC In("Books","Electronics"),Revenue,0)){~+} CaseV (case vector) CaseV  evaluates a single metric and returns different values according to the results. It can be used to perfo...
3 comments
Read more

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

MicroStrategy Connection File

Creating a Connection File in Microstrategy Workstation A MicroStrategy connection file (also know as the .mstrc) is a text file that contains all of the information needed to connect Workstation to a MicroStrategy environment.  With a .mstrc file, users can simply import the file and enter their credentials, and then they will be connected to a MicroStrategy On-Premises Environment. Requisites   MicroStrategy Workstation URL for your Environment MicroStrategy Login credentials How to create the connection file With the integrated option  Connect to New Environment: Open Workstation window. Go to  Environments  located under  Manage  on the left side.   Click on the plus symbol with the label of  Connect to a New Environment .   Assign a friendly environment name. Provide the Environment URL and click  Continue . Note: If it is a default MicroStrategy installation of MicroStrategy Library, the Environment U...
2 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

Sort by metric subtotals and attribute elements together in Microstrategy

Sort by metric subtotals and attribute elements together in Microstrategy Users may observer that when creating a report that contains advance sorting with a metric that contains subtotals the report results appear to be only sorted by the metric values specified. Even if a sort is specified for the attribute elements on the report, the results in the report appear as if the attribute sort was not defined. In the screenshot below, the results for a report are shown where the Advance Sorting option 'Sort metrics hierarchically using: Total' is selected. For this report, a second sort is defined on the Customer Gender - 'DESC' form, users would notice that the ordering of the this attribute is not consistent: The sort definition for the report is shown below: CAUSE: When the option to 'Sort metrics hierarchically using: Total' option is selected, the MicroStrategy Engine first sorts the results based on the Total values, and then sorts th...
1 comment
Read more

Custom Tooltips in Microstrategy developer and Web

Custom Tooltips in Microstrategy developer and Web The following table describes the macros you can use to customize graph tooltips in both MicroStrategy Developer and MicroStrategy Web: Macro Information Displayed {&TOOLTIP} All relevant labels and values associated with a graph item. {&GROUPLABEL} Name of the graph item's category. This value is often the graph item's attribute element information, as attributes are commonly used as the categories of graph reports. {&SERIESLABEL} Name of the graph item’s series. This value is often the graph item's metric name information, as metrics are commonly used as the series of graph reports. {&VALUE} The value of a given data point. {&XVALUE} The X-value of a data point. Only applicable to Bubble charts and Scatter plots. {&YVALUE} The Y-value of a data point. Only applicable to Bubble charts and Scatter plots. {&ZVALUE} The Z-value of a data point. Only applicable to Bubble charts and Scatter plots. {...
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

MicroStrategy default sort order for an attribute elements browsing

MicroStrategy default sort order for an attribute elements browsing and display How does MicroStrategy 9.x resolve the default sort order for an attribute when different sort orders are defined for different forms? Consider the following cases: CASE 1 A new attribute is created with three forms, all with sort order set to none. Form Name Form Type Default Sort Order ID ID None DESC DESC None LongDesc None None The overall sort order is evaluated and stored in the attribute definition when the attribute is saved. With all form sort orders set to none there is no saved sort order, MicroStrategy defaults to sort ascending by ID. CASE 2 The same attribute is modified so the forms are now: Form Name Form Type Default Sort Order ID ID None DESC DESC Descending LongDesc None Ascending Now when the attribute is saved, MicroStrategy goes through each form in the order they appear in the main 'Forms' window of the attribute editor. The first...
2 comments
Read more