Complex Ranking/Top N% problem

[Former Member]

I have a fairly complex scenario that we need to present on a dashboard.

We need to be able to visualize the following. The data will be sourced from a BEx query, where we will have a vendor and spend available. My first challenge is getting the spend broken down into top 5%/Other. Making it even more unique is that the definition of top 5% is based on vendor count, not by spend. So using a top N condition in a reference query won't help (I don't think).

The sample data below highlights what we're trying to accomplish.

I'm looking for options here to be able to deliver the above result. We have the ability to create one or more BEx queries if that would help.

Some options we've considered:

1. Create a custom datasource and derive the values in script.

2. I don't think that Former Member topics on this will satisfy the requirement because of the complex way that we want to derive top N.

I'm still new to design studio, so I may be overcomplicating this. Ideally we'd be able to change the data in the datasource (we could include some dummy KF's in a BEx query), so that way we could bind that data to any control, but I'm not completely tied to that. I'm looking to understand how others might approach this in terms of visualization. For example, can we create a table control and populate that via script without modifying the datasource contents (which I don't think I can do).

Feel free to point me to similar links to review (although I promise, I have searched ).

[IngoH · Accepted solution]

Hi Richard,

did you try:

- first create a Local Calculation in the BEx query which would be either a Rank or an Olympic Rank based on the Spend

- Then setup a condition based on that local calculation ?

regards

Ingo Hilgefort

[Former Member · Accepted solution]

Ingo,

Thanks for taking the time. You too Tammy.

I'm with you on this. We're actually trying it as we speak. That said, my gut feel is it will lead to a road block. I'm just not sure that the calculate single value result will flow into the BOBJ layer or work as a condition for that matter (although the fact that you suggested it tells me it probably will ), as the underlying data is still the spend amount.

Even then, I don't really want a condition per se, because that would filter the results. We MAY (it's a long shot I think) be able to create two queries, one with the top-N% condition on the ranked spend, and the other with two structures using the list of vendors from the first as the driver for the row level restrictions (one inclusive; one exclusive ... yuck). We will give this a shot though and see if we can make something like that work. I'll respond either way.

If the rank does flow into the BOBJ layer, that would at least help solve the ranking issue, but we would still need to derive the top N%/bottom N% in scripting somehow. I'm almost certain we won't be able to base a CKF in BEx on the ranked value (again, fairly certain it's display only). So we couldn't create a formula to leverage the ranked value.

I will play with this, but still open to suggestions on approach. Again, anything goes here. Just because I'm stuck in a paradigm doesn't mean the SCN world should be .

[IngoH · Accepted solution]

Hi Richard,

the local calculation and condition are supported by Design Studio and Analysis Office and I was under the assumption that we talking about Design Studio here.

ingo

[Former Member · Accepted solution]

Hmm ... Ok. Yes, we are talking DS 1.6 for sure. We'll give that a shot for sure and see how far it takes us.

[Former Member]

I should add that I haven't ruled out the custom datasource option, but I'd rather avoid the custom extension at this point if possible, solely due to the dynamics of this client. I may very well present it if folks think that's the ideal way to go.

Ideally, we'd use standard controls with some script to accomplish the result, but I will propose this option too.

[TammyPowlas]

Hello Richard,

I'm not following the vendor count; it is the # of times you have done business with a vendor or?

Is the count in your BEx query too?

[Former Member]

In the above example, I used 15 vendors (probably should have been more creative with my vendor #). 5% of 15, rounded up, is 1. So, In this case we're looking for vendors ranked 1 or lower by spend in the top 5%. If for example, vendor 2 had a spend of 9999, it wouldn't show up in the results, because it would have been ranked 2.

Did that clarify your question?