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