Skip to Content
author's profile photo Former Member
Former Member

How to create a division in text component with scrip/ Design Studio


I have been trying to create a script as formatter function. I want to show the sum of a measure (stores sales) divided by the total stores, for example:

Total Sales: 1,000

Total Stores: 10

The number I would like to see is 100 (1,000/10) in a text component.

I am using this script: var mydiv = GRUPO.getDataAsStringExt("ALCANCE",{"TIENDAS":"T008;T011"}); return mydiv; First, I should bring the sum of the sales, then the total stores and finally create the division, isn't it?

Add comment
10|10000 characters needed characters exceeded

2 Answers

  • Best Answer
    Posted on Feb 20, 2017 at 05:16 PM

    Hi Alejandra,

    Thanks for reposting the images. Although you have obtained a result from your calculations, I'm not sure yet whether it is necessarily the correct result. Can you clarify the following?

    1) What type of data source are you using: Universe, BW BEx Query or HANA View?

    2) As I understand it from your previous comments, ALCANCE represents the "sales percentage" for a store. What is the exact definition of this sales percentage and how is it calculated?



    Add comment
    10|10000 characters needed characters exceeded

    • Hi Alejandra,

      Thanks for the clarification. There are two considerations here:

      1. Technical Implementation of division in a formatter function

      In this particular case, your requirement is to perform a calculation that relies on several input parameters. Therefore, as I indicated in my original response, for a cleaner solution you should NOT use a formatter function but instead a standard Global Script function which includes ALL of the input parameters required for the calculation. This way the function is self-contained. The example you have implemented technically yields a result but the approach is not best practice coding because the formatter function is not self-contained.

      Also, it appears that you you are using the count of getMembers() to obtain the store count for a large number of stores. This is not efficient from a performance perspective. Furthermore, since getMembers() returns the members from master data values rather than the data source result set, you may not necessarily obtain the correct store count with this approach. It is better to obtain all fields necessary for the calculation from the data source result set.

      2. Correct calculation of Total Sales Percentage

      It is important to consider whether the calculation being performed is meaningful from a business perspective and yields the correct result so that correct decision making information is conveyed to the business.

      You have indicated "The number I wanted to show is the percentage of all the company (average) so that's why i bring all the Alcance divided by all the stores so show an average.". However, if you need the "percentage of all the company" (which in fact is the correct approach), you should keep in mind that this is NOT THE SAME as the AVERAGE, which will yield an incorrect result.

      The following factors should be considered:

      i) The current calculation is determining the average sales percentage by summing the percentages and then dividing by the store count. This is like averaging averages, which as a rule should never be done because it does not provide a meaningful result. Similarly, summing percentages that do not add to 100% is meaningless and results in an incorrect average store calculation.

      ii) Presumably what you are trying to do is determine a way to compare each store's sales performance or contribution to the overall sales performance of the company. Therefore, instead of calculating an average store sales percentage, you should be calculating Total Company Sales Percentage = Total Real Sales / Total Target Sales. This is the figure that each store should compare to for measuring their performance against the overall company performance instead of comparing against the average percentage, which you should notice yields a different result.

      iii) My understanding from your comments is that the Sales Percentage field ALCANCE is a calculated field in your HANA view. If this is the case, then the "Resultado" and "Resultado Total" lines should be showing the calculated total percentage instead of the sum of each of the store percentages which is what appears to be happening now based on the 2,949.17 value. Can you confirm the configuration of the "Calculate Totals As" option for the ALCANCE field in the data source Initial View? It should be set to "default" so that it picks up the calculation from the HANA view. If this is done correctly, then you don't even need to do a calculation via scripting. You can just directly bind the "Resultado" line value of field ALCANCE to the text field.



  • Posted on Feb 17, 2017 at 02:40 AM

    Hi Alejandra,

    A formatter function is not appropriate for your scenario because you are attempting to perform a calculation based on two values and assign the result to the text component, rather than formatting the assigned value or performing a calculation just on the assigned value, strictly speaking. Since the Text component can only be bound to a single result set cell, the formatter function will receive only one value as an input parameter whereas you require two input parameters to calculate the final "formatted" value.

    In your case I suggest you simply define a regular global script function as follows:

    1. Define appropriate input parameters, including the text component, measure technical IDs etc;

    2. Use GRUPO.getData().value to separately retrieve the Total Sales and Total Stores values, then perform the calculation in a script variable;

    3. Assign the calculated script variable to the text component using setText().

    If you can provide a screenshot of the Initial View of your GRUPO data source I can provide a more specific example.



    Add comment
    10|10000 characters needed characters exceeded