Display Countries According to Revenue Input

[Former Member]

Hello Everyone,

We have predefined list of countries and their revenue. I am looking for a functionality where a user gives a random number of his choice in a input box. then the excel has to pick the countries that are less than the revenue given and write them to seperate cells, then a spreadsheet table has been setup to show the resultant countries to the user. Here I have included few screenshots.

Pre-Defined Revenue values:

So according to this, 43270 is revenue given by user and the excel has to write, Germany Spain and Norway to seperate cells.

Any inputs are greatly appreciated. Thanks in advance

[Former Member · Accepted solution]

Hi R V,

Lets presume that in your picture the countries are in column A and the figures in column B.

In column C add a formula which returns 1 if the number in column B is higher than the target (random number entered by user) and 0 if it is not. The formula for USA should be =if(B2< 43270,0,1).

Once the third column contains 0 or 1 depending if the number is lower/higher create a combo box which only picks up the records with a 0. (labels should be 0 and 1, Filtered Rows, Item = 0)

Good luck with your dashboard and if you have additional questions please do not hesitate to contact me,

Regards,

Matt

[Former Member · Accepted solution]

Here's an Excel-based solution.

Result:

Formulas:

[Former Member · Accepted solution]

In addition to my answer:

1. Make sure in the formula you don't hardcode the number. the formula should compare the figure for each individual country with the figure from the destination of the text box.

2. Create a Lower and Higer section (like in my picture) - this will allow you to tell the Combo Box if you want to display the values which are higher or lower than the target (do this in the Behaviour item of the section --> Item --> bind to G2 if you want lower or to H2 if you want higher.

3. All the other settings of the combo box and the formula can be seen in the picture.

4. Good luck!

Regards,

Matt

[Former Member]

Hello Everyone,

Sorry for responding late. Firstly a big thanks for your help. Here is what I have done,

I used if statements to achieve the result (can be seen in Column E). Later to aviod the gaps in between, i used a list builder component, Column E (E4:E10) will dynamically feed the list bulider component and the component destination will be G4:G10, Labels= B4:B10, Selection type=Labels, .(list bulider will be hidden eventually). Let me know if it is not clear or if there is any drawback with this process.

your screenshots do not show formula you have used. Can you please provide the formula you used to acieve the result.

[Former Member]

Hi RV,

Best practice in Dashboards says that you should use the Excel bit within your Dashboard as little as possible as it decreases performance (the amount of entries on SCN regarding dashboards taking too long to run is a good indicator of what I just said) . Using complex IF statements or other calculations (like Vlookups) within the Excel bit further slows your dashboard down and that is what you are heading towards. You might not notice it now as you are probably building a POC but when using proper data this is what happens.

Not sure why you have decided against using my solution - it is as straight forward, easy to develop and effective as it can be. It also gives you the flexibility to easily choose if you want if you want to display data which is over or under your target and most importantly makes use of the product (why bothering using SAP Dashboards if you hardcode formulas into Excel?)

Kind regards,

Matt

PS: Just in case my comment sounds familiar, please see below. Even that question has a status of "Assumed Answer" - not really fair for people who reply spend their time replying to your posts

[Former Member]

You were right Matt, relying on excel formulas could affect the performance, but I did not think the process I followed is a performance killer. Your solution is absolutely straight forward and I followed my process before I get a chance to look at your reply. Now that I knew it would be a performance issue, I will change it right away.

Thank you very much for your time and support.

[Former Member]

Hi RV,

One more alternate solution.

Output:

Formulae:

Regards,

Aashutosh