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?
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
something must be wrong, please see attachment and thanks for your help
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
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
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