Analysis Office Data Analysis Advanced Calculations

Since Analysis Office 2.1 you have a formula editor. You can find this function in the group Data Analysis >> Calculations >> Add Advanced Calculation.

Analysis Office Add new advanced calculation
Analysis Office Add new advanced calculation

With this function you can create complex calculations, as you could make earlier in a BEx Query. You can find mathematical functions, data functions, percentage functions and Boolean operators.

 

Besides addition and subtraction, you can find functions like logarithm to the base of 10 or decimal part of operand.

 

In the data functions you will find something like "value without dimension" or "NOERR(X): Equals 0 for undefined calculations; otherwise x" or "NDIV0(x): Equals 0 When Divided by 0; otherwise x. The Boolean operators are found such functions as "AND" or "EXCLUSIVE OR"

 

I think the new calculations are great, I haven't longer to overfill the single query with dozens of calculations and I only need to build the calculations in the Workbook where I needed them. But this is the greatest disadvantage too. When I need some calculations often, I have to create them each time.

 

Nevertheless, I am a friend of simple queries, because they are most immediately understandable.

author.


I am Tobias, I write this blog since 2014, you can find me on twitter and youtube. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.

Analysis Office - The Comprehensive Guide 3rd edit
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.6 and contains 272 Pages.
49,99 €  
Analysis Office - The Comprehensive Guide 2nd edit
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.4 and contains 243 pages.
39,99 €  
Analysis Office - The Comprehensive Guide
Analysis Office - The Comprehensive Guide is a pdf book about SAP BusinessObjects Analysis Office. It is based on Analysis Office 2.3 and contains 227 Pages.
9,99 €  

Write a comment

Comments: 6
  • #1

    dissatisfied (Tuesday, 21 February 2017 16:39)

    what's the point of Boolean operators when there is no "IF" function

  • #2

    Tobias (Tuesday, 21 February 2017 20:06)

    Hi,

    the IF-function is build in another way. This is how you build a IF-Then:
    <Expression1> + NOT <Logic Expression> * <Expression2>

    Tobias

  • #3

    Gabriel (Tuesday, 23 January 2018 21:00)

    Hello,

    Is there a way to reference a cell in the workbook through those calculations? For example, if you want to apply a custom rate to a measure that would be calculated in another sheet of the workbook.

    Thank you,

  • #4

    Tobias (Wednesday, 24 January 2018 13:52)

    Hi,

    for this you should use the table design and add a new column/row. Now you can use VLOOKUP to apply your custom rate. Have a look at https://www.youtube.com/watch?v=qxjIx_Rp128

    Tobias

  • #5

    Fardeen (Friday, 16 March 2018 07:50)

    Hi Tobias,

    I encounter an issue when inserting a new column as an Advanced Calc in a workbook. The issue is when I press Promp to display the filter panel, and then press OK the dataset is reset and the output loses any type of filter (FIlter by Member) or Default Values Filter apply on the BEX query.
    This issue is on AO 2.6 SP1.

    Thank you

  • #6

    Tobias (Friday, 16 March 2018 15:13)

    Hi Fardeen,

    to get it right. You have an applied filter for a dimension and add then the advanced calculation. And after you applied the advanced calculation the crosstab is reseted?