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 levels. | Note |
|---|
It is recommended to get familiar with memberValue and crossValue functions before proceeding with this tutorial. |
In the table below, you can see two drill down levels (Employee Name and Product). Avg. Rating is 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 added as a conditional formatting, so if the employee's avg. rating on a product 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. | Code Block |
|---|
if(!equals(substringBefore(memberValue(), ' ') ,'Product')) {
return '';
} else {
filter("L_PRODUCT in ('" + memberValue() + "')") {crossValue('CUBE_DAILY_CALLS.DAILY_CALLS','M_RATING@AVG')};
}
|
| Note |
|---|
It is recommended to get familiar with crossValue formula before proceeding 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 employee average on the product. Changing the order of the drill downs will make calculating *Avg. Benchmark" a little bit different. The code is as below: | Code Block |
|---|
double benchmark = 0;
if(equals(substringBefore(memberValue(), ' '), 'Product')
|| isBlank(memberValue())) {
benchmark = M_RATING@AVG;
}else {
benchmark = prev('M_RATING@AVG',1);
}
return benchmark
|
Next Steps |