5 Replies Latest reply: Aug 3, 2007 4:20 PM by 3004 RSS

    HowTO Guide: Time Balance Average in ASO

    3004
      <p>I thought it would be nice to give back to the Hyperioncommunity, with a quick little snippet of code that took me quite awhile to figure out.  What I was trying to do was create aTime Balance Average function for an ASO cube  (similar to TBAverage in an Measure tagged with the type "Account" in aBSO cube).  TB Average is not a builtin function of ASO.</p><p> </p><p>Basically here is the steps I followed to once again get aTBAverage equation working in ASO.</p><p>1.  I tagged certain members in my dimension named"Measures" (of the type Accounts) , with a UDA called"<b>TBAvg</b>", these are members that I want to have theoption to see the average if viewing across several months (such asa Quarter rollup).</p><p> </p><p>2.  Within the Dimension "Month"  (which isof the type Time).  The structure looks like this....</p><p>  + Month Time Multiple Hierarchies Enabled <3>(Label Only)</p><p>      <span style=" color:#0000ff;">  + Months Stored (+) <12></span></p><p><span style=" color:#0000ff;">        + FYDynamic (+) <4></span></p><p><span style=" color:#0000ff;">               +Qtr 1 (+) <3></span></p><p><span style=" color:#0000ff;">                    +Jan (Shared Member)</span></p><p><span style=" color:#0000ff;">                    +Feb (Shared Member)</span></p><p><span style=" color:#0000ff;">                    +Mar (Shared Member)</span></p><p><span style=" color:#0000ff;">                +Qtr 2 (+) <3></span></p><p><span style=" color:#0000ff;">                +Qtr 3 (+) <3></span></p><p><span style=" color:#0000ff;">                +Qtr 4 (+) <3></span></p><p><span style=" color:#0000ff;">        + YTDDynamic (+) <12></span></p><p> </p><p><span style=" color: #000000;">I then changed the properties ofQtr 1 and created the following formula....</span></p><p><span style=" color: #000000;">Case<br>WHEN IsUDA([Measures].CurrentMember, "TBAvg")<br>THEN<br>    Avg ([Qtr 1].children)<br>END</span></p><p> </p><p><span style=" color: #000000;">I then repeated the process forQtr 2, Qtr 3, Qtr 4, Jan YTD, Feb YTD...etc....by changing the partthat says "Avg([Qtr1].children), to whatever it is you are trying to average. Reload the cube, and your all set.</span>  Check yourresults.</p><p> </p><p>I hope this quick ASO Guide was useful.  This Hyperioncommunity is great, but I think more simple code like this thatactually works, will benefit the community even more.  Takecare.</p><p> </p><p> </p><p> </p><p> </p>