Displaying variance between computed columns

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

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

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