cancel
Showing results for 
Search instead for 
Did you mean: 

Top N ranking in design studio by scripting

Former Member
0 Kudos

Dear All,

I'm trying to get Top 5 Material Group in an info-chart by using scripting.

To achieve this, I am referring to this blog (https://blogs.sap.com/2016/06/14/implement-top-nbottom-n-in-sap-design-studio-using-bial-script/).

If I sort descending based on CY- Actuals in edit initial view, I get the following results, where the top 5 are 0143, 0279, 0030, 0204 and 0009.

However, by applying the scripting as below, I get different Top 5(0001, 0003, 0002, 0009, # not assigned). The top 5 returned are the first 5 Material Groups sort ascending by key and it rank the first 5 material group according to the CY-Actuals.

Can anyone tell me how can I get the top 5 according to CY- Actuals for all material groups instead of the first 5 material group?

Thank you.

Regards

Pei Shi

MustafaBensan
Active Contributor
0 Kudos

Hi Pei. What is your backend data source? From the script it looks like it might be BW. Is that right?

Former Member
0 Kudos

Hi Mustafa,
Yes, the data source is BW query.

0 Kudos

Hi Pei Shi Hee,

We are facing the same issue, did you find any solution?

Thanks,

Susan

Accepted Solutions (0)

Answers (2)

Answers (2)

stephlef
Participant
0 Kudos

Hello Former Member,

If you're still struggling after 6 years, which I doubt as you seem to be a former member, or if anyone else searches for a solution, here's one based 100% on Lumira BIAL scripting, which works for me. I was working with complex Universes I couldn't change, which is why I went for a Top 5 with scripting.

Of course you'll need a DS_1 with values for the top N Dimension you want to do a top N for in your Application.

Best Regards,

Stéphane

var members = DS_1.getMembers("0DIMENSION", 100000);
var stopLoop2 = false;
var arrTopValues = [0.0, 0.0, 0.0, 0.0, 0.0];
var minTopValues = 0.0;
var minTopValuesIndex = 0;
var arrTopMembers = ["a", "b", "c", "d", "e"];
var currValue = 0.0;

members.forEach(function(element, index) {
	currValue = DS_1.getData("0MEASURE", {"0DIMENSION": element.internalKey}).value;
//add value and store member if value is superior, at the smallest value of arrTopValues if(minTopValues < currValue) { arrTopValues[minTopValuesIndex] = currValue; arrTopMembers[minTopValuesIndex] = element.internalKey; // new Min value has to be calculated! arrTopValues.forEach(function(element2, index2){ //find a value which is lower or equal to all others if ((stopLoop2 === false) //for bigger Top N (here N = 5) comparisons, could replace with a loop (using a counter after condition met. If counter = length of table, then replace then minValue...) && (arrTopValues[index2] <= arrTopValues[0]) && (arrTopValues[index2] <= arrTopValues[1]) && (arrTopValues[index2] <= arrTopValues[2]) && (arrTopValues[index2] <= arrTopValues[3]) && (arrTopValues[index2] <= arrTopValues[4]) ){ //change index and value of minimum value, and stop loop APPLICATION.log("Case 2 element > old MinValue: " + minTopValues + " index:" + minTopValuesIndex + " new MinValue: " + element2 + " index: " + index2); minTopValues = element2; minTopValuesIndex = index2; stopLoop2 = true; } }); stopLoop2 = false; } }); //Set that filter! DS_1.setFilter("OBJ_20", arrTopMembers);
Vidhya
Active Participant
0 Kudos

Hi Pei Shi Hee,

We have two options,

1. In design studio , while adding the data source, In Edit Query Specification, we can added the ranking filter in the Query Filter panel itself, Please find the below screenshot,

It will work for any Data Source.

After adding this, try to add this script,

	var top10cust=DS_1.getMembers("_eaTRsbh0EeWuS70gbqxHFA", 10);
	DS_1.setFilter("_eaTRsbh0EeWuS70gbqxHFA",top10cust);
	DS_1.sortByMeasure("_NZW_IM7vEeWmlZgk6MSEDA", true);
	TOP10_CHART.setDataSource(DS_1);

2. If it is BW , Using Bex Condition, we can achieve this,

http://visualbi.com/blogs/design-studio/general/working-with-bex-conditions-in-sap-businessobjects-d...

Regards,

Vidhya.C