Skip to main content

Using linear and seasonal regression for Revenue Forecasting

Revenue forecasting example (using linear and seasonal regression)

To aid in setting goals for next year, you would like to establish a forecast of your company's revenue based on existing trends.
You will use the revenue values already available in your MicroStrategy project. Predictions of future revenue will be determined at quarterly intervals. The quarters for the future dates are already defined in your MicroStrategy project, but revenue figures for each are not yet available.
Your model will require a continuous input of quarters to recognize a regression pattern. A Quarter attribute is commonly formatted to represent a year followed by a quarter, and therefore cannot be used as a continuous index into the quarters. To address this requirement, create a Quarter Index metric using the Quarter attribute. Since a year followed by a quarter is still a sequential list of values, you can often create a Quarter Index metric with the following simple expression:
Rank(Max(Quarter@ID) {Quarter})
This uses the ID values for a Quarter attribute, and creates a sequential list of values by ranking them. For example, assume you are using values dating back to the beginning of 2012. Quarter 201202 (Q2 in year 2012) has an index of 2 since it is the second quarter overall. Quarter 201403 (Q3 in year 2014) has an index of 11.
If the ID values for your attribute representing quarter data are not sequential, you must determine a way to convert the ID values into a sequential list of values.
The example Tutorial project includes a Quarter Index metric, along with other reports and metrics created for this forecasting example. Some of the definitions of metrics and reports within the Tutorial project are different than the simplified descriptions in this example. You can use the definitions provided in the steps here, or the definitions in the Tutorial project, depending on what works best for your reporting environment.
To begin your data mining analysis, use the Training Metric Wizard to design a training metric, following the steps described below.

To create a training metric for linear regression analysis

1In MicroStrategy Developer, select Training Metric Wizard from the Tools menu. The Training Metric Wizard opens on the Introduction page.
2Click Next. The Select Type of Analysis page opens.
3Select Linear regression as the type of analysis.
4Click Next. The Select Metrics page opens.
5Add Revenue as the Dependent Metric.
6Add the Quarter Index metric to the list of Independent Metrics.
7Clear the Show advanced options check box to use the default settings for variable reduction and other variable settings.
8Click Next. The Select Output page opens.
9Select the Automatically create on report execution check box.
10Within the Predictive metrics to generate area, select Predicted Value.
11Click Finish to save and create the metric.
For more information on the Training Metric Wizard, see Creating a predictive model using MicroStrategy or refer to the MicroStrategy online help.
Next, create a report that includes the new training metric and the Quarter attribute. Include the Revenue metric to compare the values calculated by the training metric with the original values. Review the Report Data Options and VLDB properties for your report to ensure that outer join results are displayed for the metrics of your report. Execute the report.
The training metric generates a straight line that best fits the Revenue data. The report, converted into a dashboard to display this trend, is shown below.
A predictive metric is created in the folder you specified in the Training Metric Wizard. The default location is the My Objects folder.
The predictive metric accurately depicts a linear line through the Revenue data, but for this example, assume that you are not satisfied with the predictions. Your data is seasonal and you need to forecast the fluctuations that will occur throughout the year.
Seasonality is recognized by adding another independent metric to the training metric. This additional metric specifies the quarter within the year associated with each Quarter Index value. For example, the Quarter Index values of 1, 5, and 9 are all from the first quarter. The Quarter of Year metric uses the same basic formula as Quarter Index. The BreakBy parameter is defined as year so that the ranking is restarted for each year, allowing each quarter to be numbered 1 through 4 for a given year. The formula is shown below:
Rank<BreakBy={Year}>(Max(Quarter@ID) {Quarter} )
To include seasonality in your data mining model, complete the following steps.

To add seasonality to the data mining model

1In MicroStrategy Developer, double-click the training metric you created in To create a training metric for linear regression analysis to open the Training Metric Wizard to the Introduction page.
2Click Next. The Select Type of Analysis page opens.
3Do not change any of the values on this page. Click Next. The Select Metrics page opens.
4Add the Quarter of Year metric to the list of Independent Metrics, which already includes the Quarter Index metric.
5Click Next. The Select Output page opens.
6Rename the predictive metric so that the existing linear predictive metric is not overwritten.
7Save the training metric with a new name to distinguish it as a seasonal prediction.
For more information on the Training Metric Wizard, see Creating a predictive model using MicroStrategy or refer to the MicroStrategy online help.
You can now re-execute the report you created earlier that included the training metric, Quarter, and Revenue. The results of the training metric now recognize the fluctuations in revenue throughout each year and predict values accordingly. Notice that the data accounts for seasonality and is no longer a straight line, as shown in the report below.
A predictive metric is created in the folder you specified in the Training Metric Wizard. The default location is the My Objects folder.

Comments

Post a Comment

Popular posts from this blog

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

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

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 "Error type: Odbc error. Odbc operation attempted

 "Error type: Odbc error. Odbc operation attempted: SQLExecDirect. [HYT00:0: on SQLHANDLE] [MicroStrategy][ODBC Oracle Wire Protocol driver]Timeout expired" is shown when executing reports from Web When users are trying to execute some reports in MicroStrategy web in particular, they may receive the Error “SQL Generation Complete Index out of range” and “Timeout expired” error as shown below: Possible Causes: One possible cause is that the MicroStrategy Intelligence Server using a cached database connection that was already dropped by the RDBMS. To resolve this: Admin should delete the database connection caches and create a new DSNs in case they are sharing DSNs to connect to different databases. In addition, change the settings for the ‘Connection lifetime’ and the ‘Connection idle time out’.  Follow the steps below to perform the mentioned changes and verify the report after each step and some of the settings require i-server r...
4 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

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

Enable Incremental fetch in MSTR documents

Enable Incremental fetch in MSTR documents Incremental fetch divides large documents or layouts into pages, thereby loading the data in batches (or blocks) rather than all at the same time. This improves the usability and performance of a large document or layout, by reducing the load and overall memory usage on the web server. To apply incremental fetch to a document In MicroStrategy Web, open the document in the Document Editor. If the document contains multiple layouts, select the layout to apply incremental fetch to. From the  Tools  menu, choose  Document Properties . The Document Properties dialog box opens. On the left, under Layout Properties, select  Advanced . Select the  Enable Incremental Fetch  check box. From the  Fetch Level  drop-down list, select the object to be counted for the incremental fetch level. If the document or layout is grouped, the groups are displayed in the drop-down list. Groups that are displayed...
1 comment
Read more

Certify dossiers in MicroStrategy

Certify Dossiers in MicroStrategy Web Dossiers can be certified for an environment by users with certain permissions. Certified items have typically been reviewed by trusted members of your organization and are considered official sources of content, based on reliable data. Two of the Security Role which have the ability to certify dossiers: Application Administrator  - Users granted this role have access to all application specific tasks. Certifier  - Users granted this role can certify objects in addition to the authoring capabilities. Follow the steps  below   with a user who has   Certifier privileges  which has been added newly Users without certifier/application administrator privileges will not see the option to certify. In MicroStrategy Web, right-click on a dossier and select Properties.   Check the Certified option and click Ok.   Navigate to MicroStrategy Library and users will see the orange certified flag...
Post a Comment
Read more

Microstrategy Removing sections that do not have metric data

Removing sections that do not have metric data This is an interesting feature which might not be explored by many of us and it comes us handy. A  cross join between datasets can result in rows or Group Header/Footer sections that do not have metric data. For example, a document contains two datasets. Dataset 1 contains Year and Revenue, with data for three years (2007-2009). Dataset 2 contains Year and Profit, filtered to return data for only two years (2008 and 2009). If you place Year and Profit in the Details and execute the document, it displays three rows, although no profit data exists for 2007. This is a product of the cross join between the two datasets. You do not want to see the blank line for 2007 since it does not give you any data for profit. You can select the  Trim sections for which no metric value data is available  check box. This removes the row for 2007, since no metric data for Profit is available for 2007. The results are shown below: ...
Post a Comment
Read more

Multi-Select Drop Down Selector with Apply Button

Multi-Select Drop Down Selector with  OK Button  When creating a Report Services Document, you may want to create a drop down selector to save space, but also would like the drop down to be a multi-selector with OK/Apply button. Below are instructions to achieve this in a Report Services Document.  Steps to Create: 1. Right click on your drop down selector and choose Properties and Formatting 2. Navigate to the Layout Tab 3. In the layout tab, click "Allow multiple selections". It will  not  indicate that the check box is enabled, but this step is mandatory.  3. Next, navigate to the Theme tab. Choose the  L ight Theme  from the drop down menu and click the Apply button. 4) Next, change the Theme back to  None  and click Apply. 4. When you return to the Layout tab, you will see the checkbox for  Allow multiple selections  is now checked.  
Post a Comment
Read more