1 / 39

UNIT 3 Calculation Script Architecture

UNIT 3 Calculation Script Architecture. Topics. When to use calc scripts Calc Script Editor and online help Database, dimension and member calculations Focus calculations Generate member lists Boolean functions Mathematical functions. Introducing Calc Scripts. Components of calc scripts

curry
Download Presentation

UNIT 3 Calculation Script Architecture

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. UNIT 3Calculation Script Architecture

  2. Topics • When to use calc scripts • Calc Script Editor and online help • Database, dimension and member calculations • Focus calculations • Generate member lists • Boolean functions • Mathematical functions

  3. Introducing Calc Scripts • Components of calc scripts • Calc Script Editor and online Help • Commands and functions

  4. Using Calc Script Editor (Demo)

  5. Using Essbase Documentation (Demo) • Essbase manuals • Product online Help • HTML formatted documentation • Technical Reference

  6. Exercise 3-1: Explore Calc Script Editor and Online Help

  7. Exploring Essbase Calc Script Functionality • Functional command and functions • Control flow commands (focus) • Macro functions • Conditional commands • Boolean functions • Operators • Math functions

  8. Using Functional Commands • CALC ALL • CALC DIM • MEMBER CALC • Other CALC script commands

  9. (CALC ALL) Low Level data loaded Calculating the Database Using CALC ALL • Entire database is calculated. • Data is calculated based on outline consolidations and member formulas. • Syntax:CALC ALL [EXCEPT DIM(dimList)| MBR(mbrList)];

  10. (CALC DIM) Low Level data loaded Calculating Dimensions Using CALC DIM • Calculates a specified dimension or a list of dimensions • Calculates first dimensions tagged as Accounts and as Time • Calculates dense dimensions before sparse • Syntax:CALC DIM(Accounts);

  11. Calculating Members Using Member Calc • Unary operators and equations • All database combinations for referenced member • Syntax: MemberName ;

  12. Exercise 3-2:Use Calc Functions

  13. Focusing Calculations • FIX…ENDFIX • IF…ELSE…ELSEIF…ENDIF • CROSS DIM OPERATOR ->

  14. FIX (East) Budget Actual East South North West Jun Feb Mar Jan Focusing Calculations Using Fix…Endfix • Isolates a portion of the database • Syntax: FIX(mbrlist) commands; ENDFIX

  15. Focusing Calculations Using Conditional Statements • IF…ENDIF • IF…ELSE…ENDIF • IF…ELSEIF…ENDIF • IF…ELSEIF…ELSE…ENDIF

  16. IF condition is TRUE then do statement. IF condition is FALSE, then skip statement. Focusing Calculations Using IF...ENDIF Performs conditional tests before performing a calculation • Commission (IF(Sales > 200000) • Commission = Sales * .012 ; • ENDIF)

  17. Focusing Calculations Using ELSEIF…ELSE Performs conditional test if IF statement is FALSE • Commission • (IF(Sales >= 200000) Commission = Sales * .12; ELSEIF (Sales >= 100000) Commission = Sales * .098; • ELSE Commission = Sales * .05; ENDIF)

  18. Sales->Jan->Actual Budget Actual Sales COGS Margin Margin% Jun Feb Jan Mar Focusing Calculations Using Cross Dim A pointer to data values of specific member combinations Syntax:->

  19. Exercise 3-3:Focus Calculations

  20. Family @CHILDREN @DESCENDANTS @SIBLING @RELATIVE Generation and level @GENMBRS @LEVMBRS Others @UDA @MATCH Generating Lists Using Macro Functions

  21. Given this outline FIX(@ICHILDREN(West)) … ENDFIX Applies calculations to the following: West, California, Oregon Generating Lists Using @ICHILDREN Includes the specified member and all members in the level immediately below the specified member

  22. Given the outline FIX(@IDESCENDANTS(WEST)) … ENDFIX returns the following: West,California,San Diego,San Francisco, Los Angeles, Chico, Oregon Generating Lists Using @IDESCENDANTS Includes the specified member and either all descendants or only the descendants down to a specified generation or level.

  23. Given the outline, FIX(@UDA(MARKET,”Small Market”)) … ENDFIX applies calculations to the following: Connecticut, New Hampshire, and South Generating Lists Using @UDA Generates a list of members that have the referenced UDA

  24. Generating Lists Using @MATCH • Allows you to perform a wild-card search for members Given the following members FIX(@MATCH(PRODUCT,”???-10”)) … ENDFIX Returns the following: 100-10,200-10 100-10 100-20 200-10 200-30 300-20

  25. Exercise 3-4:Using Macro Functions

  26. Testing for Values Using Boolean Functions • @ISDESC • @ISMBR • @ISLEV • Logical connectors

  27. Testing for Values Using @ISDESCENDANTS Tests member during calculation to determine appropriate formula for payroll • Payroll • (If (@ISDESC(East) OR @ISDESC (West)) • Payroll = Sales * .15; • ELSEIF (@ISDESC (Central)) • Payroll = Sales * .11; • ELSE • Payroll = Sales * .10; • ENDIF)

  28. Testing for Values Using @ISMBR Tests to see if a member is present for a specific calculation Fix (East) Commission ( If (@ISMBR(New York: New Hampshire)) Commission = Commission * 1.25 ; Endif ) Endfix

  29. Testing for Values Using @ISLEV Tests to see if a member is present for a specific calculation If the calculation is positioned on New York: @ISLEV(“Market”,0) is TRUE and @ISLEV(“Market”,1) is FALSE

  30. Using Logical Connectors with Boolean Functions • AND • OR • NOT

  31. Exercise 3-5:Using Boolean Functions

  32. @VAR @TRUNCATE @MIN @MOD @VARPER @ROUND @MAX @ABS Transforming Values with Math Functions

  33. @VAR Calculates the variance (difference) between the values of two members @VAR(Actual,Budget) @VARPER Calculates the variance (difference) percent between the values of two members VARPER(Actual,Budget) Transforming Values with @VAR and @VARPER

  34. @TRUNCATE Removes the fractional part of an expression and returns the integer. Total_Sales = @TRUNCATE( @SUM(Direct_Sales: Other_Sales)); @ROUND Rounds an expression to the nearest whole number or to a specifically decimal Profit = @ROUND("Profit_%”); Transforming Values with @TRUNCATE and @ROUND

  35. @MIN Returns the minimum value among the results of an expression Qtr1 = @MIN(Jan:Mar) ; @MAX Returns the maximum value among the results of an expression Qtr1 = @MAX(Jan:Mar) ; Transforming Values with @MIN and @MAX

  36. Transforming Values with @MOD • Calculates the modulus (the remainder) of a division operation Factor = @MOD("Margin %", "Profit %"); • “Margin %” = Dividend “Profit %” = Divisor

  37. Transforming Values with @ABS • @ABS returns the absolute value of an expression. Variance = @ABS(Actual-Budget) ; • The absolute value turns negative numbers into positive numbers: @ABS(-5) = 5 @ABS(+5) = 5

  38. Exercise 3-6:Math Functions

  39. Test Your Understanding

More Related