The following example is based on the ASOsamp.Sample database. To remove ambiguity in query results, the example uses the solve order property to specify the required calculation priority.
The spreadsheet query in Figure 166, Results from Spreadsheet Query of ASOsamp.Sample database Showing the Variance Between Two Ratios (C12) retrieves data for the number of units sold and the number of transactions for January of the current year and for January of the previous year. The Variance member shows the difference between the current year and the previous year. The Avg Units/Transaction member shows a ratio of the number of units sold per transaction.
Figure 166. Results from Spreadsheet Query of ASOsamp.Sample database Showing the Variance Between Two Ratios (C12)
Figure 167, ASOsamp.Sample Database Showing the Measures, Years, and Time Dimensions shows the database outline for these members and the formulas applied to the Variance and Avg Units/Transaction members.
When calculating the variance of the average units per transaction (cell C12 in Figure 166, Results from Spreadsheet Query of ASOsamp.Sample database Showing the Variance Between Two Ratios (C12)), the result could be the variance between the two ratios, or the result could be the ratio of the two variances. The result depends on whether Essbase gives precedence to the formula on Variance or the formula on Avg Units/Transaction.
The value of the solve order property, which is attached to the members in the database outline, determines the priority with which Essbase evaluates the formulas. The formula on the member that has the higher solve order takes precedence.
In the example, if the Variance member has a higher solve order than the Avg Units/Transaction member, then the formula on the Variance member takes precedence and the result is the variance between two ratios. This is the case in the ASOsamp.Sample database, because the solve order of the Variance member is 20 and the solve order of the Avg Units/Transaction member is 10. The formula on Variance takes precedence, because the Variance member has the higher solve order. The result for cell C12 of the query in Figure 166, Results from Spreadsheet Query of ASOsamp.Sample database Showing the Variance Between Two Ratios (C12) is the variance between the two ratios, as shown in Table 206:
Table 206. Using the Solve Order Property to Specify the Variance Between Two Ratios
Result of Intersection of Variance and Avg Units/Transaction (cell C12 in Figure 166, Results from Spreadsheet Query of ASOsamp.Sample database Showing the Variance Between Two Ratios (C12)) | |||
---|---|---|---|
Current year average units/transaction - previous year average units/transaction 0.94894382 (cell C6) - 0.954252111 (cell C9) = -0.005308291 (cell C12) | |||
Alternatively, if you change the ASOsamp.Sample database, and you give the Avg Units/Transaction member a higher solve order than the Variance member, then the formula on the Avg Units/Transaction member takes precedence, and the result is the ratio of two variances, as shown in Table 207 and in Figure 168, Results from Spreadsheet Query of ASOsamp.Sample Database Showing the Ratio of Two Variances (C12):
Table 207. Using the Solve Order Property to Specify the Ratio of Two Variances
Result of Intersection of Variance and Avg Units/Transaction (cell C12 in Figure 168, Results from Spreadsheet Query of ASOsamp.Sample Database Showing the Ratio of Two Variances (C12)) | |||
---|---|---|---|
Variance (current year to previous year) of units / variance of transactions 10585 (cell C10) / 11340 (cell C11) = 0.933421517 (cell C12) | |||