Forum Discussion

lalasisi's avatar
lalasisi
Cloud Apps
09-06-2024

Tricky Combo Chart in Sisense How?

Hi the great sisense community, I need a lot of help on making a combo chart in Sisense. Below is what I want to create:

The purpose of this chart is to show my main baseball team (Yankees) in blue and compare its cost to other teams (orange). Due to the confidentiality of the project, I cannot show the names of the other teams in the bar chart. But I'm allowed to show their costs. 

My data looks like below and is already in sisense elasticube.

To achieve the chart in Excel, I have to make a table like below and then use this table to feed the combo chart. Main Team is the blue bar, Competitor is the orange bars and average is the average line.

My chart configuration as below:

Now comes the tricky question. How do I do the same thing in Sisense using my elasticube data? I have uploaded my Excel file as reference

Dashboards & Reporting
Data Models

9 Replies

  • rapidbisupport's avatar
    rapidbisupport
    Data Pipeline
    09-09-2024

    Hi lalasisi ,

    A few steps here.

    In the columns shelf you want your teams.

    In the values shelf, you want:

    • your metric for comparison &
    • your metric for comparison ALL (teams) then click on three dots > Series Type > Line.

    Lastly, this widget script will highlight the relevant team:

    let myColor = "red"
let myTeam = 'Lily Brown'

widget.on('processresult', (w, args) => {
  args.result.series[0].data.find((d) => { return d.selectionData[0] === myTeam}).color = myColor
})

    With the result looking like:

    Let me know how you go?

    Thanks,

    Daniel

    RAPID BI

    [email protected]

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

     

    • lalasisi's avatar
      lalasisi
      Cloud Apps
      09-09-2024

      Hi Daniel,

      Thanks for your reply and I appreciate the help. Your answer would help achieve the basic chart but it is missing 2 of the most critical requirements:

      1. My team would change dynamically when the end user filter to a different team (in the dashboard filter). How to write the script in a way that it will highlight a different team every time the dashboard filter is changed?
      2. How to hide the team names of all the blue bars but let the red team name visible. This also needs to be dynamic as the user can choose "red sox" this time but "yankees" next time.
      • rapidbisupport's avatar
        rapidbisupport
        Data Pipeline
        09-10-2024

        Missed these - a few extra steps required:

        Let's make a few changes to the widget script, where we will detect the selected dashboard filters related to the first category item and then change the color and replace the xAxis text appropriately:

        let myColor = "red"

widget.on('processresult', (w, args) => {
  let categoryItem = widget.metadata.panels[0].items[0].jaql.dim
  let myTeams = widget.dashboard.filters.item(categoryItem).jaql.filter.members
  for (let i = 0; i < myTeams.length; i++) {
    let myTeam = myTeams[i]
    args.result.series[0].data.find((d) => { return d.selectionData[0] === myTeam }).color = myColor
  }
  for (let j = 0; j < args.result.xAxis.categories.length; j++) {
    if (!myTeams.includes(args.result.xAxis.categories[j])) {
      args.result.xAxis.categories[j] = ''
    }
  }

})

        Secondly, lets make sure the widget filters behaviour is set to 'Highlight':

        This should result in this here:

        Let me know how you go?

        Thanks,

        Daniel

        RAPID BI

        [email protected]

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

  • rapidbisupport's avatar
    rapidbisupport
    Data Pipeline
    09-11-2024

    Hey mate,

    Sure thing - i've provided a few variables at the top of the script to change selected and unseelcted colors:

     

    let selectedColor = "#F7921E"
let unselectedColor = "#00ACCD"

widget.on('processresult', (w, args) => {
  let categoryItem = widget.metadata.panels[0].items[0].jaql.dim
  let myTeams = widget.dashboard.filters.item(categoryItem).jaql.filter.members
  
  args.result.series[0].data.forEach((d) => { 
	  d.color = unselectedColor
	  d.selected = false
  
  })
  
  for (let i = 0; i < myTeams.length; i++) {
    let myTeam = myTeams[i]
    args.result.series[0].data.find((d) => { return d.selectionData[0] === myTeam }).color = selectedColor
  }
  for (let j = 0; j < args.result.xAxis.categories.length; j++) {
    if (!myTeams.includes(args.result.xAxis.categories[j])) {
      args.result.xAxis.categories[j] = ''
    }
  }

})

     

    With the value labels, you can do this in the interface (the "Value Label" toggle):

    Let me know how you go?

    Thanks,

    Daniel

    RAPID BI

    [email protected]

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

     

    • lalasisi's avatar
      lalasisi
      Cloud Apps
      09-11-2024

      Thank you! Is there a way to only show value labels for the bars, not the line?

      • rapidbisupport's avatar
        rapidbisupport
        Data Pipeline
        09-12-2024

        Yep - you can remove them by using the args.result.plotOptions in the process result:

          args.result.plotOptions.line.dataLabels.enabled = false

        Or all together:

        let selectedColor = "#F7921E"
let unselectedColor = "#00ACCD"

widget.on('processresult', (w, args) => {
  args.result.plotOptions.line.dataLabels.enabled = false
  let categoryItem = widget.metadata.panels[0].items[0].jaql.dim
  let myTeams = widget.dashboard.filters.item(categoryItem).jaql.filter.members
  
  args.result.series[0].data.forEach((d) => { 
	  d.color = unselectedColor
	  d.selected = false
  
  })
  
  for (let i = 0; i < myTeams.length; i++) {
    let myTeam = myTeams[i]
    args.result.series[0].data.find((d) => { return d.selectionData[0] === myTeam }).color = selectedColor
  }
  for (let j = 0; j < args.result.xAxis.categories.length; j++) {
    if (!myTeams.includes(args.result.xAxis.categories[j])) {
      args.result.xAxis.categories[j] = ''
    }
  }

})

        Which looks like this:

        Let me know how you go?

        Thanks,

        Daniel

        RAPID BI

        [email protected]

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

         

  • Vadim_Pidgornyi's avatar
    Vadim_Pidgornyi
    Sisense Employee
    09-12-2024

    Hello, 

    Here is another solution here. 

    const opts = {
    targetValueDim: '[Commerce.Age Range]',
    targetColor: '#156082',
    otherColor: '#e97132',
    otherValue: 'Competitor'
    };

    const tooltipHtml = `<div class="cartesian-tooltip"><div class="cartesian-tooltip-values" ng-repeat="item in model.points"><div class="cartesian-tooltip-seriesName">{{item.seriesName | unescape}}</div><div class="cartesian-tooltip-singleLineValue" data-ng-style="{color: item.valueColor}"><div class="cartesian-tooltip-value">{{item.value | unescape}}</div><div class="cartesian-tooltip-percentage" ng-class="{disabled: item.hasExcessiveSeries}" data-ng-show="item.showPercentage">/ {{item.percentage}}</div><div class="cartesian-tooltip-disclaimer" data-ng-show="item.hasExcessiveSeries">To view as a percentage, filter the categories to contain no more than 50 results.</div></div></div></div>`;

    widget.on('beforeviewloaded', (scope, args) => {

    const { targetValueDim, targetColor, otherColor, otherValue } = opts;
    const targetValueFilter = widget.dashboard.filters.item(true, targetValueDim);

    try {
    const targetValues = $$get(targetValueFilter, "jaql.filter.members");

    args.options.series[0].data.forEach((point) => {
    const isTarget = targetValues.includes(point.selectionData[0]);
    point.color = isTarget ? targetColor : otherColor;
    if (!isTarget) {
    point.selectionData[0] = otherValue;
    }
    });

    args.options.xAxis.categories = args.options.xAxis.categories.map((category) => {
    return targetValues.includes(category) ? category : otherValue;
    });

    } catch(err) {
    console.error("Error processing filters: ", err);
    }
    args.options.plotOptions.line.dataLabels.enabled = false

    });

    widget.on('beforedatapointtooltip', (scope, args) => {
    args.template = tooltipHtml;
    });

    Kind regards