data manipulation in Pivot chart

[harish]

I have build the pivot chart with x axis country in values sales , sales percentage  , Now i want to give these two columns as same name like single name and then it will bifurcate , common name to be given is Sales. Is it possible to do this

[DRay]

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]

I want to group >50k and 50k perc should share same column name 50k similarly for 10k-50k and 10k-50k perc into 10k-50k.
the requirement is column name should be same but the values should be in different columns

[DRay]

To make sure I understand, you want to span a single column header across two columns?

If so, would something like this work for you?

[harish]

Yes this would work , and can i also sort it according to my custom order , like Alaska , Arizona ,alabama, American samao?

[DRay]

You should be able to. Can you test it and let us know how it goes?

[harish]

My widget is coming like this , i think bit different from the screen snip you shared ? and in my widget i cannot sort it

[DRay]

Ok. If that's not working I would like to get someone on a call with you to go over this in person. Please reach out to your Sisense account team to get connected with a resource who can work through a solution with you.

[rapidbisupport]

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

[DRay]

@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.