Since Analysis Office 2.1 you have a formula editor. You can find this function in the group Data Analysis >> Calculations >> Add 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.
These posts might also be intersting:
I am Tobias, I write this blog since 2014, you can find me on twitter, facebook and youtube. I work as a Senior Business Warehouse Consultant. In 2016 I wrote the first edition of Analysis Office - The Comprehensive Guide. If you want you can leave me a paypal coffee donation. You can also contact me directly if you want.
- In my newsletter you get informed about new topics
- You learn how to use Analysis Office
- You get tips and tricks about SAP BI topics
- You get the first 3 chapters of my ebook Analysis Office - The Comprehensive Guide for free
You want to know SAP Analysis Office in a perfect detail?
You want to know how to build an Excel Dashboard with your Query in Analysis Office?
You want to know how functions in SAP Analysis Office works?
Then you have to take a look into Analysis Office - The Comprehensive Guide. Either as a video course or as a ebook.
Write a comment
dissatisfied (Tuesday, 21 February 2017 16:39)
what's the point of Boolean operators when there is no "IF" function
Tobias (Tuesday, 21 February 2017 20:06)
the IF-function is build in another way. This is how you build a IF-Then:
<Expression1> + NOT <Logic Expression> * <Expression2>
Gabriel (Tuesday, 23 January 2018 21:00)
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.
Tobias (Wednesday, 24 January 2018 13:52)
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
Fardeen (Friday, 16 March 2018 07:50)
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.
Tobias (Friday, 16 March 2018 15:13)
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?
Julius (Wednesday, 20 November 2019 15:22)
i have the problem that i can not click on Adv. Calculations.
The area is grayed out.
Can you help me with this issue?
Tobias (Thursday, 21 November 2019 14:50)
you have to select a key figure and then it should be available.