data manipulation in Pivot chart

Hi harish ,

If what you mean is something that looks like this:

I would recommend using a table with aggregation instead of a pivot table, as it gives you more scope to modify the payload prior to render.

The way I achieved this was to:

- add a dummy dimension after CountryName named 'Sales' (As long as it's a text field, this will work - you could even just use CountryName again),

- combine the text of the 3rd and 4th cells for each row and use this for the value of the first cell text (see the args.result.$$rows.... string operation within the script, and then

- hide the last 2 columns (with a script found from another community post linked in the script below written by rvickersevotix  - thanks!)

Please see widget script attached:

widget.on('processresult', (w, args) => {
  args.result.$$rows.forEach((r) => { r[1].text = `${r[2].text} (${r[3].text})` })
})



// credit @rvickersevotix for below to hide columns in table widget
// https://community.sisense.com/t5/build-analytics/how-to-hide-a-column-in-a-table-widget/m-p/7572
widget.on('domready', function(se, ev){

  // Specify the column indices to hide
  let columnIndices = [3, 4];

  // Function to hide columns
  function hideColumns() {
    columnIndices.forEach(function(columnIndex) {
      $(`table tr > *:nth-child(${columnIndex})`, element).css('display', 'none');
    });
  }

  // Initial hiding of columns
  hideColumns();

  // Observe changes in the table body
  const elementToObserve = $('table tbody', element)[0];
  const observer = new MutationObserver(function(e) {
    for(const m of e) {
      if (m.type === 'childList') {
        $.each(m.addedNodes, function(index, value){
          // Hide columns in added rows
          columnIndices.forEach(function(columnIndex) {
            $(value).find(`td:nth-child(${columnIndex})`).css('display', 'none');
          });
        });
      }
    }
  });

  observer.observe(elementToObserve, {subtree: true, childList: true});
});

 Let me know how you go?

Thanks,

Daniel

RAPID BI

[email protected]

RAPID BI - Sisense Professional Services | Implementations | Custom Add-ons

Hello harish.

Thank you for the question. Would you be able to provide a visual example of what you want the result to look like? That will help us understand the use case.

harish Did the solution proposed by rapidbisupport work for you? If so, please mark it as the solution so others can find the answer. If not, please let us know so we can continue to help.

Thank you.