Essbase

Get Involved. Join the Conversation.

Topic

    Ryan
    Working Day Metric
    Topic posted May 14, 2019 by Ryan, tagged Essbase, Smart View, Tip 
    74 Views, 2 Comments
    Title:
    Working Day Metric
    Summary:
    Analyzing various statistics against working days
    Content:

    We recently implemented two OAC Essbase cubes, 1 ASO and 1 BSO. Within the BSO cube, our dense dimensions are Account and Period, with the sparse dimensions as Year, Scenario, Version, Entity, Product, Client, and LOB (line of business). A useful metric for us is the concept of "Days Adjusting" some statistics to normalize the effect of differing working days across months (eg Mar19 had 21 working days while Apr19 had 22). We created a Business Day member in the account dimension, loaded to "No Product", "No Client", "No LOB", etc. Then, we developed various Days Adjusted members with stored formulas that execute upon a Calc Dim. 

    However, we analyze by client/product/LOB, so when trying to retrieve the Days Adjusted metric for a specific client, the metric retrieves as #Missing since we're trying to analyze by Client/Product/LOB etc but the Business Day component of the formula was loaded to No Client/No Product/No LOB etc. See example of formula below:

    Requests/k (Days Adjusted) = (Requests * 254 * 1000) / (Membership * Business Days)

    We have requests and membership loaded by client by product by LOB, but again, business days loaded to no client no product no LOB. Any ideas how to structure this formula in the cube? Some non-ideal workarounds might be loading the metric rather than allowing the cube to calculate it, or loading business days to each of the dimensions but then that would retrieve a very large number if a user tried to retrieve business days on its own. 

    Version:
    12.2.1.1.115
    Code Snippet:

    Comment

     

    • Ryan

      A recommendation was to use Cross-Dimensional operators. I've updated the formula to the following:

      ("Requests"*254*1000)/("Membership"*("Business Days"->"Product"->"Client"->"LOB"->"Entity"));

      and it is now working. Solution verified. Thank you for the help

    • Glenn Schwartzberg

      While you got this working, I want to propose another way to accomplish it without having to load data or do a cross dim. Make your business days metric dynamic calc and put in a formula 

      If (@ISmbr(Jan,Apr,Jun) ) 20;

      ELSEIF@ISMBR(.....) 21

      etc

      That way no mater where in the cube you are it will get he right value.

      You might have to extend it for leap years or other special instances