Page History

It is recommended to get familiar with memberValue and crossValue functions before proceeding with this tutorial.

Avg_1

 

{note}It is recommended to get familiar with memberValue and crossValue functions before proceeding with this tutorial.{note} 

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. 

{ilupng}Avg_1{ilupng}

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}
if(!equals(substringBefore(memberValue(), ' ') ,'Product')) { 
  return ''; 
} else { 
  filter("L_PRODUCT in ('" + memberValue() + "')") {crossValue('CUBE_DAILY_CALLS.DAILY_CALLS','M_RATING@AVG')}; 
}

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.

Avg_2

{code}

{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.{note}



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.  

{ilupng}Avg_2{ilupng}


The code is as below:
{code}
double benchmark = 0; 
if(equals(substringBefore(memberValue(), ' '), 'Product') 
   || isBlank(memberValue())) { 
  benchmark = M_RATING@AVG; 
}else { 
  benchmark = prev('M_RATING@AVG',1); 
} 

return benchmark
{code}

h2. Next Steps

{