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?
⋅ 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
⋅ October 20, 2020
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
⋅ October 26, 2020
Hello,
Our team is wondering whether you managed to resolve the issue.
Please let us know if everything works.
Waiting for your reply.
Kind regards,
Vera