Adding dynamic Plot Lines

Answered

Comments

6 comments

  • Avatar
    Takashi Binns

    Hi John,

    The plotlines from Highcharts don't understand Sisense functions, so you need to get the results of your query first and then use the value to draw the plot line.  The code for this is a little more complicated, but it should be something like this.  Basically, you run a separate query to get the avg and then add it to the chart.  Check out the documentation on our JAQL API for more examples of how the queries work.

    widget.on('processresult', function(widget, args) {

    // Define the JAQL query to run
    var plotJaql = {
    "type": "measure",
    "formula": "avg([00000-001],[00000-002])",
    "context": {
    "[00000-002]": {
    "table": "Order Details",
    "column": "LineTotal",
    "dim": "[Order.LineTotal1]",
    "datatype": "numeric",
    "agg": "sum"
    },
    "[00000-001]": {
    "table": "Product Subcategory",
    "column": "Subcategory",
    "dim": "[Product Subcategory.Subcategory]",
    "datatype": "text"
    }
    }
    }

    // Create the query object
    var query = {
    "datasource": widget.datasource,
    "metadata": [plotJaql]
    }

    // Define the query URL
    var url = "/jaql/query";

    // Function to handle the query result
    var addPlotline = function(response) {

    // Get the value from the response
    var valuesArray = $$get(response, "data.values", []),
    value = valuesArray.length > 0 ? valuesArray[0].data : null;

    if (value) {

    // Create the highcharts plotline array
    var plotLine = {
    color: "blue",
    width: 2,
    value: value,
    dashStyle: "dash",
    id: "customPlotline"
    }


    // Find the highcharts object within the dashboard
    var matchingCharts = $.grep(Highcharts.charts, function(w) {
    var isMatch = false;
    if (typeof w !== "undefined") {
    var isDashboard = (prism.$ngscope.appstate == "dashboard");
    var thisChartWidgetId = isDashboard ? $(w.renderTo).parent().attr("widgetid") : prism.activeWidget.oid;
    isMatch = (thisChartWidgetId == widget.oid);
    }
    return isMatch;
    });

    if (matchingCharts.length > 0) {
    // Add the plotline to the chart
    var yAxis = matchingCharts[0].yAxis[0];
    yAxis.removePlotLine(plotLine)
    yAxis.addPlotLine(plotLine)
    }
    }
    }

    // Get the $http service
    var $http = prism.$injector.get("$http");

    // Make API call
    $http.post(url, query).then(addPlotline)
    });
    1
    Comment actions Permalink
  • Avatar
    David Wit

    Will this methodology respect dashboard level filters as the user dynamically changes the filters? 

    Another way of saying that is when a user filters for only categories starting with the letter 'C', will the average line recalculate to show the average for only the filtered sub-set"  OR will the average line still show the average for the entire data set?

    0
    Comment actions Permalink
  • Avatar
    Takashi Binns

    Hi David,

    Good catch, the first example didn't take into account widget or dashboard filters.  I've added in some logic to handle this, try out the script below.


    widget.on('processresult', function(widget, args) {

    // Define the JAQL query to run
    var plotJaql = {
    "type": "measure",
    "formula": "avg([00000-001],[00000-002])",
    "context": {
    "[00000-002]": {
    "agg":"sum",
    "column":"Cost",
    "datatype":"numeric",
    "dim":"[Lead Generation.Cost]",
    "table":"Lead Generation"
    },
    "[00000-001]": {
    "table": "Lead Generation",
    "column": "City",
    "dim":"[Lead Generation.City]",
    "datatype": "text"
    }
    }
    }

    // Create the query object
    var query = {
    "datasource": widget.datasource,
    "metadata": [plotJaql]
    }

    // Define the query URL
    var url = "/jaql/query";

    // Define function to add filters
    function addFilter(filter){

    // make sure the filter has a dim
    var filterDim = $$get(filter, "jaql.dim");
    var existingDims = $.grep(query.metadata,function(w){
    return $$get(w, "jaql.dim") == filterDim;
    })

    // Only add if the filter is valid and if it hasnt already been added
    if (filterDim && (existingDims.length == 0)) {

    // Create a copy of the filter
    var newFilter = $.extend({},filter);
    newFilter.panel = "scope";

    // Add to the query
    query.metadata.push(newFilter)
    }
    }

    // Get all the filters
    var dashFilters = prism.activeDashboard.filters.$$items,
    widgetFilters = widget.metadata.panel('filters').items;

    // Loop through widget filters first
    $.each(widgetFilters,function(){
    addFilter(this);
    });

    // Loop through dashboard filters second
    $.each(dashFilters,function(){
    addFilter(this);
    });

    // Function to handle the query result
    var addPlotline = function(response) {

    // Get the value from the response
    var valuesArray = $$get(response, "data.values", []),
    value = valuesArray.length > 0 ? valuesArray[0].data : null;

    if (value) {

    // Create the highcharts plotline array
    var plotLine = {
    color: "blue",
    width: 2,
    value: value,
    dashStyle: "dash",
    id: "customPlotline"
    }


    // Find the highcharts object within the dashboard
    var matchingCharts = $.grep(Highcharts.charts, function(w) {
    var isMatch = false;
    if (typeof w !== "undefined") {
    var isDashboard = (prism.$ngscope.appstate == "dashboard");
    var thisChartWidgetId = isDashboard ? $(w.renderTo).parent().attr("widgetid") : prism.activeWidget.oid;
    isMatch = (thisChartWidgetId == widget.oid);
    }
    return isMatch;
    });

    if (matchingCharts.length > 0) {
    // Add the plotline to the chart
    var yAxis = matchingCharts[0].yAxis[0];
    yAxis.removePlotLine(plotLine)
    yAxis.addPlotLine(plotLine)
    }
    }
    }

    // Get the $http service
    var $http = prism.$injector.get("$http");

    // Make API call
    $http.post(url, query).then(addPlotline)
    });
    1
    Comment actions Permalink
  • Avatar
    Erenis Lemus (Edited )

    Hi David, 

    So If I wanted to add this dynamic average plot line to my 'Scatter Chart' or 'High Chart' all I would have to do is change this block correct? 

    // Define the JAQL query to run
    var plotJaql = {
    "type": "measure",
    "formula": "avg([00000-001],[00000-002])",
    "context": {
    "[00000-002]": {
    "agg":"sum",
    "column":"Cost",
    "datatype":"numeric",
    "dim":"[Lead Generation.Cost]",
    "table":"Lead Generation"
    },
    "[00000-001]": {
    "table": "Lead Generation",
    "column": "City",
    "dim":"[Lead Generation.City]",
    "datatype": "text"
    }
    }
    }

    **All the values to be changed are bold and italic.

    For example if I had a csv file titled "Test Results" in my elasticube with 'x' number of data columns
    three of which were titled 'identifier', 'testresult1', and 'ingredient1' and I wanted to visualize
    how the testresults1 vary by each test that has been ran ('identifier') then by using a scatter plot
    I would plot the sum of testresults1 on the y axis and identifier on the x axis.

    With this Scatter Plot I would like to add a dynamic average line that would automatically change once
    the csv file 'Test Results' is updated with new data points.

    Then I would edit the script and change the block to the following:
     // Define the JAQL query to run
    var plotJaql = {
    "type": "measure",
    "formula": "avg([00000-001],[00000-002])",
    "context": {
    "[00000-002]": {
    "agg":"sum",
    "column":"testresults1",
    "datatype":"numeric",
    "dim":"[Test Results.testresults1]",
    "table":"Test Results"
    },
    "[00000-001]": {
    "table": "Test Results",
    "column": "identifier",
    "dim":"[Test Results.identifier]",
    "datatype": "numeric"
    }
    }
    }

    and if i wanted to instead look at how the testresults1 change as the ingredient1 changes then
    I would plot the sum of the testresults1 on the y axis and the ingredients1 on the x axis and
    change the block to

     // Define the JAQL query to run
    var plotJaql = {
    "type": "measure",
    "formula": "avg([00000-001],[00000-002])",
    "context": {
    "[00000-002]": {
    "agg":"sum",
    "column":"testresults1",
    "datatype":"numeric",
    "dim":"[Test Results.testresults1]",
    "table":"Test Results"
    },
    "[00000-001]": {
    "table": "Test Results",
    "column": "ingredient1",
    "dim":"[Test Results.ingredient1]",
    "datatype": "text"
    }
    }
    }
    Is this Right? 

    Thanks


    0
    Comment actions Permalink
  • Avatar
    Takashi Binns

    Hi Erenis,

    I'm not quite sure I'm following how your data is structured or what you've got in mind for a visualization, but the sample snippets look correct assuming you want to calculate the sum of [test result] for each [ingredient], then take the average of those sums and plot them on your chart

    thanks,

    -Takashi

    0
    Comment actions Permalink
  • Avatar
    Erenis Lemus (Edited )

    Thanks. This makes more sense. The script is now working for my highcharts  

    1
    Comment actions Permalink

Please sign in to leave a comment.