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

Displaying variance between computed columns

Answered
Tyler Ellis asked on March 11, 2020

Hey there,
 
We’re trying to figure out the best way to display a percentage change between column values in one of our reports. I put together a crude JSFiddle as an example: https://jsfiddle.net/vmgtyler/ayvpdLjm/
 
Is there a way to display the variance between the summed revenue in Q1 vs Q2 (like $190 -> $225 = +18.5% for 2019)? And would it also be possible to show the variance between the two years at the row level?
 
Thanks!

5 answers

Public
Mykhailo Halaida Mykhailo Halaida Flexmonster March 12, 2020

Hi Tyler,
 
Thank you for posting your question.
 
The desired behaviour can be achieved using the “% Difference” aggregation type with the “Revenue” measure. Also, in order to analyze the revenue change for both quarters and years, they need to be put in the same field position (columns/rows).
 
We’ve prepared a quick sample based on your JSFiddle example to demonstrate this approach: https://jsfiddle.net/flexmonster/9rvL7wh3/
 
Please let us know if this helps. 
 
Best regards,
Mykhailo

Public
david March 26, 2020

Hi,
 
Piggybacking on Tyler’s question:

Is there a way to neatly wrap up custom data ranges in this way? Let’s say I have a table with two years’ worth of order data. Can Flexmonster display a custom range, such as “the first six months of 2018” vs. “the first six months of 2019”?

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster March 27, 2020

Hello, David,
 
Thank you for your question.
 
We would like to kindly suggest considering using filters in order to achieve such functionality.
We have prepared an example demonstrating the mentioned approach.
 
Our team would like to provide some additional explanation about the provided example:
 
The type date is assigned to the hierarchy named “Date” in the example. Such type allows splitting the hierarchy into three different fields: Year, Month, Day.
Now, it is possible to filter the “Month” field in the way it contains only the first six months for each year using the following filter object:

{
"members": [
"date.month.[january]",
"date.month.[february]",
"date.month.[march]",
"date.month.[june]",
"date.month.[april]",
"date.month.[may]"
]
}

 
Such filter can be applied by default in case it is specified as a value of the filter property of an appropriate element of the columns or rows arrays.
 
Also, it can be applied manually by the user using setFilter method provided by Flexmonster. In such case, the hierarchy needs to be specified as shown below:

pivot.setFilter("Date.Month", {
"members": [
"date.month.[january]",
"date.month.[february]",
"date.month.[march]",
"date.month.[june]",
"date.month.[april]",
"date.month.[may]"
]
});

 
It is also possible to use the Date Query Object that allows applying conditional filtering to date hierarchies. For example, instead of including each needed month using member property, it is possible to define the range as following:

{ "between": ["2018-01-01", "2018-06-31"] }

 
However, such filtering may be an appropriate variant for the described case due to the fact it allows specifying only one interval.
 
More information about data types in JSON can be found in our documentation.
Detailed information about filter object and its properties: Filter Object.
Documentation on Date Query Object by the link.
You may be interested in getting some more information about setFilter API as well: setFilter method.
 
We hope it works for you.
Do not hesitate to contact us in case of additional questions.
 
Best regards,
Illia

Public
david April 7, 2020

Hi Illia,
 
Thanks for the detailed response.
 
I have another follow-up. I’d like to create a report that takes a fiscal week and then creates some calculations – it will grab orders from the previous year’s fiscal week for comparison, then take calculate month-to-date and year-to-date values for the current year and the previous year. This gives me a list of time frames that I need to be able to compare against each other.
 

  • Target Fiscal Week
  • Previous Year Fiscal Week
  • Target Fiscal Month-to-date
  • Previous Year Fiscal Month
  • Target Year-to-date
  • Previous Fiscal Year To Date

 
 
 
It looks something like attached image.
 
As you can see, I’ve defined a field labeled “variance” that contains the timeframe definitions I’ve included above. This successfully gets the revenue numbers onto the report. However, I’m having trouble adding the variance numbers to the report. The %difference calculated value calculates percent variance from column to column; however, I don’t need every column compared to each other – i only need the variance between the similar pairs.
To be clear, I only need the variance between Target Fiscal Week and Previous Year Fiscal Week, Target Year to Date and Previous Year to Date, etc. The way the columns are set up, it would display the variance betwen the Previous Year Fiscal Week and Target Fiscal Month To Date, which obviously isn’t ideal.
The image I’ve attached shows the variance values that should not appear on the report.
Is there a way to accomplish this?
 

Attachments:
variance_issues.png

Public
Illia Yatsyshyn Illia Yatsyshyn Flexmonster April 8, 2020

Hello, David,
 
Thank you for your feedback.
 
We would like to kindly explain that such behavior is due to the fact that all of the time intervals display the same measure. Therefore, those intervals are members of the hierarchy “Variance”, therefore, the difference is calculated for between each member.
 
In order to get the desired view, it is possible to use one of the following approaches:

  1. Filtering members of the “Variance” hierarchy in the way only the desired ones are displayed. It is possible to provide UI controls allowing to execute the setFilter method with appropriate parameters. We have prepared an example demonstrating such an approach.
  2. Use the customizeCell API provided by Flexmonster in order to clear unwanted cells. Our team has prepared an example as well.

 
Our team would like to provide some additional explanation about the way the customizationCell API is used in the example.
Please see the code snippet taken from the second JSFiddle:

let hierarchies = ["Variance.[02_PY selected week]", "Variance.[04_PY YTD]", "Variance.[06_PY QTD]"];

function customizeCellFunction() {
  pivot.customizeCell((cell, data) => {
    if (data.type == "value" && data.measure &&
      data.measure.uniqueName == "Revenue" &&
      data.measure.aggregation == "%difference" &&
      data.columns.length > 0 && !hierarchies.includes(data.columns[0].uniqueName))
      cell.text = "";
  });
}

The hierarchies array represents the list of all members for which the difference needs to be displayed.
The customizeCell function itself invokes an arrow function that filters all cells on the grid and choose the values from the “Revenue” hierarchy with the %difference aggregation. Next, it checks whether the uniqueName of the column where the cell is placed is included in the array described before and clear the cell in case it is not.
 
Also, please note that the property caseSensitiveMembers of the options object should be set to true. In other case, names of hierarchies will be converted to lower case. It will entail the requirement to change names of the “hierarchies” array elements.
 
Detailed information about the customizeCell API can be found in our documentation.
 
Please note that such an approach is sensitive to changes in the pivot configuration, e.g., changes of the slice object. Another function may be needed in case the order of measures is changed or additional hierarchies are added on the grid.
 
Therefore, in case the grid may be changed by the user, we recommend using the first approach based on filtering.
 
We hope it works for you.
Please contact us in case of additional questions.
 
Kind regards,
Illia

Please login or Register to Submit Answer