Essbase

Get Involved. Join the Conversation.

Topic

    Kimberly Weigele
    Formula Question-Essbase
    Topic posted May 7, 2018 by Kimberly WeigeleRed Ribbon: 250+ Points, last edited August 1, 2018 by Arijit ChakrabortyBronze Trophy: 5,000+ Points 
    31 Views, 10 Comments
    Title:
    Formula Question-Essbase
    Summary:
    Formula Question-Essbase
    Content:

    I an account "Federal Income Tax" the contains a formula to calculate the value.  The end users would like the Federal Income tax to contain two different formulas depending in the product (a separate dimension).  So if Product is "A" then use formula A, if Product is "B" then use formula B.

     

    Is this doable?

     

    Thanks

    Comment

     

    • Tim German

      Yes - something like this:

      IF(@ISMBR("ProductA"))

        ...do A...;

      ELSEIF(@ISMBR("ProductB"))

      ...do B...;

      ELSE

      ...do C...;

      ENDIF

    • Kimberly Weigele

      Thank you very much!

    • ManojPavan Ch

      Hello Kimberly,

      If you are calculating only for Product A there is no need for ELSEIF and ELSE conditions.

      If you also want to include a separate formula for Product B then you need similar formula Tim had provided.

    • Kimberly Weigele

      Thank you so much for the quick response. Can I ask one more questions (for today as least) . How would I do the following formula

      Premiums (FAS 60) = equal to collected premiums when Product is A.

      I am thinking I don’t need an entire if statement as below, but could be wrong. Thanks

      Kimberly Weigele

      Senior Finance System Specialist

      Retirement Solutions Division

      Pacific Life

      949-219-7092

      kimberly.weigele@PacificLife.com<mailto:kimberly.weigele@PacificLife.com>

    • Glenn Schwartzberg

      Kimberly,

      I think we need more information.

      Are you asking for the lifetime premiumns paid or the current year premiumns?

      Are your periods and years in one or two dimensions?

      when you say the product is "A" I assume a single product, but what about the other dimensions. it it at the total of them or by individual members?

      There are different methods depending on the answers. In some cases it would not be a formula but a calc script, in other cases it could be a formula

    • Skavuri400

      just out of curiosity... why the taxes are based on product.. vs..profit made..

    • Kimberly Weigele

      It has to do with regulations, we are an insurance company and different products have to follow different accounting and tax rules.

      In the Account Dimension I have Collected Premiums and Premium (FAS 60). Premiums (FAS 60) is equal to Collected Premiums but only for Variable Products (Product A).

      Kimberly Weigele

      Senior Finance System Specialist

      Retirement Solutions Division

      Pacific Life

      949-219-7092

      kimberly.weigele@PacificLife.com<mailto:kimberly.weigele@PacificLife.com>

    • Kimberly Weigele

      Thank you so much!

      Kimberly Weigele

      Senior Finance System Specialist

      Retirement Solutions Division

      Pacific Life

      949-219-7092

      kimberly.weigele@PacificLife.com<mailto:kimberly.weigele@PacificLife.com>

    • Glenn Schwartzberg

      I guess my question would be is if it is just collected premiums for Product A why do you need to assign it to a different member and calculate it? why not just look at Collected Premiums for Product A .

    • Skavuri400

      1. For the tax member Tim's formula will work...

      IF(@ISMBR("ProductA"))

        ...do A...;

      ELSEIF(@ISMBR("ProductB"))

      ...do B...;

      ELSE

      ...do C...;

      ENDIF

      2. Premiums (FAS 60) = equal to collected premiums when Product is A...

          per Glenn's comment ... you can just do this in smartview query by  using collected premiums and product A combination..

          if NOT

               you need a separate member in the accounts dimension which does not roll into net income.. as dynamic calc...

                       formula could be a variation of  

                                       "collected_premiums"->"ProductA"

                       the above formula shows the premium in unwanted intersections but is a start...

    • Brian Somers

      So something like this for the account "Premiums (FAS 60)":-

      IF(@ISMBR("ProductA"))

      "Collected Premiums";

      ELSE

      ...

      ENDIF

      The ... is for whatever you need to assign if it is not "ProductA". Also you should probably make the account dynamic as it will improve performance (as long as accounts are dense)