Despite the COVID-19 outbreak, our team continues operating at full speed. We are always here to support and answer all your questions.

Feel free to reach out by filling this quick form.

Fill the form
Get Free Trial

Formulas are summing instead calculating.

Answered
Marketing Scripts asked on October 10, 2020

Hi, 
 
I’m trying to use formulas but it`s not working, it’s summing the values instead of calculating it. (In WebData Rocks was working)
 

    var pivot = new Flexmonster({
container: "pivot-container",
componentFolder: "https://cdn.flexmonster.com/",
licenseKey: "{{ flexmonster_key }}",
toolbar: true,

report: {
formats: [
{
decimalPlaces: 2,
thousandsSeparator: ".",
decimalSeparator: ','
},
{
name: "revenue",
currencySymbol: "R$"
},
{
name: "integers",
decimalPlaces: 0,
},
{
name: "percent",
currencySymbol: "%",
currencySymbolAlign: 'right'
},
],
dataSource: {
dataSourceType: "json",
data: arr_data
},
slice: {
rows: [
{% if crossun %}
{
uniqueName: "Company"
},
{% endif %}
{
uniqueName: "Ano"
},
{
uniqueName: "Mês"
},
{
uniqueName: "Dia"
},
{
uniqueName: "Dia da Semana"
},
{
uniqueName: "Promoção"
},
{
uniqueName: "Título"
},
{
uniqueName: "Tipo",
filter: {
members: [
"Tipo.loja",
],
negation: true
}
}
],
columns: [{
uniqueName: "[Measures]"
}],
expands: {
expandAll: false,
rows: [
{
tuple: [
"Ano.[{{current_year}}]"
]
}
]
},
measures: [{
uniqueName: "Pedidos",
format: "integers",
formula: "sum(\"Pedidos\")",
},
{
uniqueName: "PPE",
caption: "PPE(x1000)",
formula: "1000*(sum(\"Pedidos\") / sum(\"Envios\"))",
},
{
uniqueName: "Receita",
format: "revenue",
formula: "sum(\"Receita\")",
},
{
uniqueName: "RPE",
caption: "RPE(x1000)",
formula: "1000*(sum(\"Receita\") / sum(\"Envios\"))",
format: "revenue",
},
{
uniqueName: "Opens",
format: "integers",
formula: "sum(\"Opens\")",
},
{
formula: "100*(sum(\"Opens\") / sum(\"Envios\"))",
uniqueName: "Open Rate",
format: "percent",
individual: true,
active: true

},
{
uniqueName: "Envios",
format: "integers",
formula: "sum(\"Envios\")",
},
{
uniqueName: "CTR",
formula: "100*(sum(\"Clicks\") / sum(\"Envios\"))",
format: "percent",
},
{
uniqueName: "CTOR",
caption: "CTOR",
format: "percent",
formula: "100*(sum(\"Clicks\") / sum(\"Opens\"))",
},
{
uniqueName: "Sessões",
format: "integers",
formula: "sum(\"Sessões\")",
},
{
uniqueName: "Conversões",
formula: "100*(sum(\"Pedidos\") / sum(\"Sessões\"))",
format: "percent",
},
{
uniqueName: "Opt-Out Rate",
format: "percent",
formula: "100*(sum(\"Opt-Out\") / sum(\"Envios\"))",
},
{
uniqueName: "TKM",
formula: "(sum(\"Receita\") / sum(\"Pedidos\"))",
}],
},
options: {
configuratorActive: false,
showAggregationLabels: false,
defaultHierarchySortName: 'asc',
datePattern: 'yyyy/MM/dd',
dateTimePattern: 'yyyy/MM/dd',
}
},
width: "100%",
height: 500,
});

 
The OR should be opens/sent but it’s not calculating right. If I use the same formula via toolbar it works. Can you help me?

2 answers

Public
Vera Didenko Flexmonster October 13, 2020

Hello, 
 
Thank you for writing to us and for providing your report configuration. 
 
We would like to explain that we have not managed to reproduce the issue on our end using a sample data set: both Flexmonster and WebDataRocks seem to produce the same result.
 
To avoid possible misunderstandings, please see the following JSFiddle that we have created in the process: https://jsfiddle.net/flexmonster/tkz52wfj/
In the example, we created a calculated value based on the formula of "Open Rate" that you provided:

{
formula: "100*(sum(\"Opens\") / sum(\"Envios\"))",
uniqueName: "Open Rate",
format: "percent",
individual: true,
active: true
}

Instead of "Opens" and "Envios" we used "Price" and "Quantity"

{
uniqueName: "OR1",
formula: "100 * ( sum(\"Price\") / sum(\"Quantity\") )",
individual: true,
caption: "OR (individual: true)"
}

As a result, both Flexmonster and WebDataRocks calculate the formula the same. For example, "OR (individual: true)" for "Accessories" is calculated in the following way: 

635.1111111111111 = ( (174 / 225) * 100 ) + ( (502 / 90) * 100 )

The "OR (individual: false)" calculated value shows how the formula will be calculated if individual is set to false

214.6031746031746 = ( (174 + 502) / (225 + 90) ) * 100

 
Could you please confirm if this is the result you are looking for? If there is anything that we are missing, please let us know and modify the provided JSFiddle. This will greatly help us to understand the situation better.
 
Looking forward to your reply.
 
Kind regards, 
Vera

Public
Vera Didenko Flexmonster 2 days ago

Hello, 
 
Our team would like to kindly take an interest in whether you managed to resolve the initial issue.
Did it work to configure the formulas?
Please let us know if there is still anything we can assist you with here.
 
Looking forward to your feedback.
 
Kind regards, 
Vera

Please login or Register to Submit Answer