In our previous blog, we outlined a way to achieve visualization for Top 5 & ‘Others’ through Query Designer and Lumira Designer (formerly known as BusinessObjects Design Studio). This approach is a great workaround to answer a very common request from users- provided that N (number of top/bottom values) is less than 20, and the ranking does not change dynamically based on users’ filter selection.

Please make sure you have read our previous blog before continuing.

If you have implemented the above-mentioned solution, you will notice that the top/bottom 5 customers are not sorted in the order of ascending/descending profitability. That is because when you run this script,

, the getMembers() method returns the top 5 customers in alphabetical order from the master table and then passes this to the Query 3 variable. For our Top 5 customer to show up in ranked order, we need to pass the customer values to Query 3 in an ascending or descending order of its profitability. To do that, we will be following these steps:

1. Get all the Profitability values from query to and store it in a float array. Then we need to rank this array in an ascending /descending order, based on our requirement. The index of the float value will then be sorted in the order we want.
2. Get all the Customers members values and store in a text array (this will be in alphabetical order).
3. Loop through this customer array and use getData() method to compare the data value with a value in the float array in step 1. If we get a match in value, the index of float value will be the new order position for our customer.

Here are the scripts to achieve that (in addition to the queries created with the above-referenced blog post)

1. Create a Global Script called CALCULATION, and a function called rank() under it with the following script:

The script will take a float number and a float array, then find a position for the float in the array to make the array sorted.

var len = floatArray.length;
var arrayCopy = [1.0]; arrayCopy.pop();
floatArray.forEach(function(element, index) {
arrayCopy.push(element);
});
if(len == 0){
});
floatArray.push(number);
}
else{
if(number < floatArray[0]){
floatArray = [number];
arrayCopy.forEach(function(element, index) {
floatArray.push(element);
});
}
else if(number > floatArray[len-1]){
}else{
floatArray.forEach(function(element, index) {
var curIndex = index;
var n2 = floatArray[index+1];
if(number > n1 && number < n2){
floatArray[curIndex+1] = number;
arrayCopy.forEach(function(element, index) {
if(index > curIndex){
}
});
}
});
}
}
return floatArray;

2. Create the following script to sort a given float array using the function rank() above.

var newArray = [1.0]; newArray.pop();
floatArray.forEach(function(element, index) {
newArray = CALCULATION.rank(newArray,element);
});
return newArray;

3. Here is the script to replace the final script in the previous blog – passing customers values to Query 3’s variables.

var topcustomers = DS_1.getMembers(“ZR_CUST”, 5);//Getting top 5 members from Query 2
var variables = [“ZKAR_VAR_C1″,”ZKAR_VAR_C2″,”ZKAR_VAR_C3″,”ZKAR_VAR_C4″,”ZKAR_VAR_C5”]; //All variables for Query 3
var customerKeyRanked = [”]; customerKeyRanked.pop(); //Create a place holder array.
var profitValues = [1.0]; revenueValues.pop();
topcustomers.forEach(function(element, index) {
profitValues.push(DS_1.getData(“”,{“(MEASURES_DIMENSION)”:<Technical Key of Profitability>,”ZR_CUST”:element.internalKey}).value);
}):
var profitValuesRanked = CALCULATION.sortFloatArray(revenueValues);//Sort all profitability values in the array.
topcustomers.forEach(function(element, index) {
var key = element.internalKey;
var profit = DS_1.getData(“”,{“(MEASURES_DIMENSION)”:<Technical Key of Profitability>,”ZR_CUST”:key}).value;
profitValuesRanked.forEach(function(element, index) {
if(element == revenue){
customerKeyRanked[index] = key;
};
}):
}


//Pass sorted customer member values to the variable

variables.forEach(function(element, index) {
APPLICATION.setVariableValueExt(element, customerKeyRanked[index]);

Learn more about Visual BI’s SAP Lumira Offerings here.