Dear visitor, despite the COVID-19 outbreak, our team continues operating at full speed.

Also, here is the form where you can apply for a special discount and we will contact you with possible options. Stay safe and continue achieving your business goals.

Fill the form
Get Free Trial
Get Free Trial

how to use %difference

Answered
andres kosest 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?
 

5 answers

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster 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

Public
andres kosest February 7, 2020

something must be wrong, please see attachment and thanks for your help

Attachments:
flexnotes.doc

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster 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

Public
andres kosest 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

Attachments:
pivot.docx

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster 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

Please login or Register to Submit Answer