Hello,
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?
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?
Regards,
Mustafa.
Hello Mustafa,
1. I am using HANA view
2. The sales percentage (ALCANCE) is calculated in the hana view according the target versus the real sales.
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.
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.
Regards,
Mustafa.
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.
Regards,
Mustafa.
Thanks Mustafa!
I tried to do it but it didn't work.
This is my initial view of GRUPO data source where TIENDAS are the stores and ALCANCE are the sales percentage.
I tried this script to get the total value of the sales of all the stores but an error appears:
GRUPO.getData("ALCANCE",{"TIENDAS": "T001-T999"}).value;
Can you show the full script code in the function without the error message obscuring it?
Is this:
GRUPO.getData("ALCANCE",{"TIENDAS": "T001-T999"}).value;
Alejandra,
There are a number of problems here:
1. You must specify a Return Type of "none" since we will perform all the processing within the function;
2. You have not specified any input parameters to allow the calculation to be performed and assigned to a text component, as per my suggestion;
3. Why are you specifying the "T001-T999" range? You cannot specify a range in the getData() method as it returns only a single value;
4. How do you intend to perform your calculation of Total Sales / Total Stores in this function, taking into consideration the GRUPO data source Initial View structure?
Regards,
Mustafa.
Hello Mustafa,
I changed the initial view and I added a column that brings the summ of all the stores
Then I changed the script in the global variable:
And finally I got the result:
I dont know if is the easiest way to get the info but at least it works, what do you think?
Hi Alejandra,
I cannot see the images you have inserted. Can you paste them again?