asked on February 5, 2020
i've seen that between the formulas there is a %difference function which, probably, should compute the difference % between one cell in a column and the cell in previuos one (if exists)
imagine we have such a table:
items vs sales by year
2018 2019
itm1 10 20
itm2 30 15
the %difference formula should add a column (only one because there is no previous for the first column) like
2018 2019 delta
itm1 10 20 100%
itm2 30 15 -50%
where is the syntax of such a formula in the manuals?
⋅ February 6, 2020
Hello, Andres,
Thank you for contacting us.
We would like to provide some explanation about the way the %difference aggregation works. E.g., in case your slice object is similar to the following,
columns: [
{ uniqueName: "Year" },
{ uniqueName: "[Measures]" }
],
rows: [
{ uniqueName: "Item" }
],
measures: [
{ uniqueName: "Price" },
{
uniqueName: "Price",
aggregation: "%difference"
}
]
the %difference aggregation result will be a difference between years. In case the difference between items is required, measures should be placed in the array with an element representing items:
rows: [
{ uniqueName: "Items" },
{ uniqueName: "[Measures]" }
],
However, we would like to kindly inform you that such a measure will be placed in rows instead of columns.
Please check out an example demonstrating the described approach.
We hope it helps.
Please contact us in case of additional questions.
Best regards,
Illia
⋅ February 7, 2020
something must be wrong, please see attachment and thanks for your help
⋅ February 7, 2020
Hello, Andres,
Thank you for your feedback.
The reason for the obtained result is the fact the { uniqueName: "[Measures]" } object is placed in columns, where the object { "uniqueName": "Anno" } locates. That entails counting the %difference based on the "Anno" hierarchy.
The required output uses "categmerce" hierarchy as a base for %difference aggregation. In order to achieve such a behavior, values have to be placed in the same array with the hierarchy it has to be based on:
"rows": [
{ "uniqueName": "macrocateg" },
{ "uniqueName": "categmerce" },
{ "uniqueName": "[Measures]" }
]
We hope it helps.
Kind regards,
Illia
⋅ February 12, 2020
thanks Illia, yes, it works when moving measures into rows section, but it's not very readable, especially if we add more than one measure, ie value and quantity, and %difference computed on both. with a standard columnar approach we just add two columns per main columnar section. while with the proposed approach we multiply by 4 all the rows available.
in the attachment i show you what can be easily obtained with excel or with an old visual basic cube object (by the way directely connected to the sql source).
i add another issue related to row filtering.
thanks
⋅ February 12, 2020
Hello, Andres,
Thank you for sharing your ideas with us.
However, for now, the way %difference aggregation works is not going to be changed. We can recommend preprocessing the data set so it contains the desired column containing appropriate difference.
As for filtering, our team would like to kindly inform you that such behavior is a known usability issue. Even so, for now, it is the way filtering works in Flexmonster.
You are welcome to contact us in case additional questions appear.
Kind regards,
Illia