Date: Fri, 29 Mar 2024 12:31:32 +0000 (UTC) Message-ID: <1810701734.4218.1711715492202@localhost> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_4217_641763540.1711715492201" ------=_Part_4217_641763540.1711715492201 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
It happens very frequently that we use a few drill downs in our = tables, and we want to calculate the average of indicators at different lev= els.
It is recommended to get familiar with memberValue and crossValue functi= ons before proceeding with this tutorial.
In the table below, you can see two drill down levels (Employee = Name and Product). Avg. Rating i= s calculating the average rating for that employee on that product.
But you may want to compare the employee's average rating for a product = with the overall employee rating for the same product as a benchmark, as it= is shown in the Avg. Benchmark column. This could be adde= d as a conditional formatting, so if the employee's avg. rating on a produc= t is higher than overall employee's rating on this product, we can mark it = as green and show an upper arrow there.
Here is how you could calculate this.
if(!equ= als(substringBefore(memberValue(), ' ') ,'Product')) {=20 return '';=20 } else {=20 filter("L_PRODUCT in ('" + memberValue() + "')") {crossValue('CUBE_DAILY_= CALLS.DAILY_CALLS','M_RATING@AVG')};=20 }
It is recommended to get familiar with crossValue formula before proceed= ing with this tutorial. As crossValue will not take drill downs applied in = the context view.
In this table below, now Product is being placed as the= first level of drill downs, and then Employee Name is the= second level. Avg. Benchmark is still the overall employe= e average on the product. Changing the order of the drill downs will make c= alculating *Avg. Benchmark" a little bit different.
The code is as below:
double = benchmark =3D 0;=20 if(equals(substringBefore(memberValue(), ' '), 'Product')=20 || isBlank(memberValue())) {=20 benchmark =3D M_RATING@AVG;=20 }else {=20 benchmark =3D prev('M_RATING@AVG',1);=20 }=20 return benchmark