1 / 22

UNIT 4 Calculation Script Architecture – Advanced Functions

UNIT 4 Calculation Script Architecture – Advanced Functions. Topics. Range functions Relationship functions Iterative processing Simultaneous equations Environmental settings Variables. Exploring Advanced Functions and Commands. Range functions Relationship functions Iterative commands

hadar
Download Presentation

UNIT 4 Calculation Script Architecture – Advanced Functions

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 4Calculation Script Architecture – Advanced Functions

  2. Topics • Range functions • Relationship functions • Iterative processing • Simultaneous equations • Environmental settings • Variables

  3. Exploring Advanced Functions and Commands • Range functions • Relationship functions • Iterative commands • Environmental commands

  4. Mathematical @SUMRANGE @AVGRANGE @MAXRANGE @MINRANGE Exceptions @CURRMBRRANGE @PRIOR Financial @SLN @IRR @COMPOUND Calculating Values Using Range Functions

  5. Calculating Values Using @SUMRANGE Sums values of a specified member across a range Example: Q1_SALES = @SUMRANGE(SALES, JAN : MAR) ; Result: Q1_SALES = 600 Sales: Jan: 100 Feb: 200 Mar: 300

  6. Calculating Values Using @AVGRANGE Averages values of a specified member across a range Example: Q1_AVGSALES = @AVGRANGE(SKIPMISSING,SALES, JAN : MAR) ; Result: Q1_AVGSALES = 200 Sales: Jan: 100 Feb: #MI Mar: 300

  7. Calculating Values Using @MINRANGE and @MAXRANGE Supplies minimum or maximum value of a member across a range Examples: Q1_MIN = @MINRANGE(SALES, JAN : MAR) ; Q1_MAX= @MAXRANGE(SALES, JAN : MAR) ; Results: Q1_MIN = 100 Q1_MAX = 300 Sales: Jan: 100 Feb: 200 Mar: 300

  8. Calculating Values Using @CURRMBRRANGE • Returns a member list based on the relative position of the member being calculated • Can be used only with range functions Example: Six_Month_Total = @SUMRANGE (Sales, @CURRMBRRANGE(Year, LEV, 0,-5 , 0)) /"Average Inventory” ;

  9. Calculating Values Using @PRIOR Returns the value from the previous or the nth previous member in a range of members By default uses the lowest level of a dimension tagged as Time Example: Inventory = @PRIOR(End_Inv) ; Result: Jan Feb Mar Apr May Jun Inventory 1100 1100 1200 1000 1300 1300 End_Inv 1100 1200 1000 1300 1300

  10. Exercise 4-1:Calculating Values Using Range Functions

  11. Obtaining Values Using Relationship Functions • @PARENTVAL • @ANCESTVAL • @MDPARENTVAL

  12. Obtaining Values Using @PARENTVAL Returns the immediate parent value of a specified member combination Example: PRODUCT_MIX=SALES % @PARENTVAL(PRODUCT,SALES); Result: PRODUCT_MIX is calculated for New York PRODUCT_MIX = 50% Given EAST = 100,000, New York = 50,000,

  13. Obtaining Values Using @ANCESTVAL Returns a higher level value of a specified member combination based on specified generation or level Example: PRODUCT_MIX=SALES % (@ANCESTVAL(PRODUCT,2,SALES)); Result:PRODUCT_MIX = 10 % Given Colas (Gen 2) = 100,000, Diet Cola (Gen 3) = 50,000, Kool Kola (Gen 4) = 10,000

  14. Obtaining Values Using @MDANCESTVAL • A higher level value of a specified member combination, based on position specified across multiple dimensions • References any dimension and any generations • Marketing = (Sales / @MDANCESTVAL(2, Market, 2, Product, 2, Sales)) * @MDANCESTVAL(2, Market, 2, Product, 2, Marketing);

  15. Exercise 4-2:Obtaining Values Using Relationship Functions

  16. FIX(New_York,Camera,Actual,Mar) LOOP(30) Commission = Profit * .15; Profit=Margin-Total_Expenses-Commission; ENDLOOP ENDFIX Controlling Execution Using Loop…Endloop Use the LOOP...ENDLOOP command to specify the number of times to iterate a group of calculations. Syntax:

  17. Explaining Simultaneous Equations • Unknown values exist on either side of the equal sign. • A = B * .15, where A and B are unknown. • B = y - A, where y is data and A and B are unknown.

  18. Exercise 4-3:Using Loop…Endloop to Solve Simultaneous Equations

  19. Setting the Calculator Environment • SET commands • Enhance log information • Determine Intelligent Calculation behavior • Manage memory • Variable declarations • Scalar • Arrays • Substitution variables

  20. Using Set Commands to Set the Calculator Environment • Information SET MSG SET NOTICE • Intelligent Calculation SET UPDATECALC SET CLEARUPDATESTATUS • Optimization SET AGGMISSG SET CACHE SET LOCKBLOCK SET FRMLBOTTOMUP SET CALCHASHTBL • Set commands are procedural

  21. Using Data Declarations to Set the Calculator Environment • VAR • ARRAY • Substitution variables

  22. Check Your Understanding

More Related