Okay
  Public Ticket #3171166
The problem with creating a table and a chart with highlighting % values, negative values, as well as highlighting the highest and lowest values in a given row of the table with color
Closed

Comments

  • Andrzej Trzcinski started the conversation

    HI. In fact, I'm wondering if your plugin could solve my problem. I want to publish a sales report compared to last year's results. Report summary in the format of days of the week, where in the first table for sales results, and in the second table expressed in % increase or decrease compared to the previous year. I am sending a screenshot of what it should look like.
    I bought your plugin but I can't get it to look like this. I have a question, will your plugin allow me to create such a report?

    I also have another graph that I would like to create using your plugin, but I also have a problem, and I don't know how to deal with creating a graph.
    Are the graphs shown on the screen possible to do with your plugin?
    I am interested in creating charts in which in a given table I can highlight the highest, lowest and negative values with color, as shown on screen No. 1 in the table with %

    Charts and tables must be updated daily, here another question arises about the possibility of updating data, for example, from a OneDrive or SharePoint cloud source?
    I can't find any tool to enable this in your plugin.

    My main question is can I achieve such graphs and table layout with your plugin? And if so, please advise me on how I can achieve this result.

    Best Regards 
    Andy

    Attached files:  1.JPG
      2.JPG

  • Andrzej Trzcinski replied

    Hi. I am hoping someone will answer my service ticket regarding the ability to create data tables and charts with your plugin that compare sales periods in both integer and %

    I'm also interested in the currency indicator, but I think I've already found how to create it.

    As you can see, the data must be updated daily, preferably from an external source, for example SharePoint.
    As it looks in the screenshot I attached.

    In the table on the right, using Java script, I change the color in the row when it comes to the lowest values, the highest values, and also negative values. Unfortunately, my old plug in is not compatible with PHP 8.1
    So, I have a specific question if I am able to achieve the same effect for charts as well as for the appearance of the table.

    I also did not find a tool that would allow you to download data from a MS Excel file with multiple tabs. My old plugin had such an option and it made it very easy to import multiple tables from one file at the same time, instead of, for example, 3 different ones.

    Regards

    Attached files:  Graf.JPG

  •  1,667
    Miloš replied

    Hi, Andrzej 

    Firstly, I would like to sincerely apologize for the delayed response as we have been experiencing an unusually high number of tickets. I am sorry that it has taken longer than usual to respond to your concern and your patience is highly appreciated.

    -

    We will now cover all questions on the same ticket, but for the future, please note :

    When you have questions or issues which are not related to the title of the active ticket, please open a new ticket, and we will help you there. In that way, issues and questions which are related to different subjects will be in separate tickets so other customers or our support agents can find them easily. Our policy is to have one issue or question per ticket because of the reasons that are described already.

    Thank you for understanding.

    -

    I also have to point out that our Support does not cover fully building a solution for you,

    but we can advise with examples as much as possible on how something is made,

    and if it's possible with our plugin.

    1. 

    6759102089.png

    In terms of having the different colours of cells for different numbers,

    you can use our Conditional Formatting to achieve that for the tables.

    Please check more on that here.

    -

    2. 

    But, we will not be able to pass any colour from the values in the table, or any Conditional Formatting,

    to the chart.

    Also, it is not possible to pass any suffix/prefix from the table to the charts.

    I will elaborate more on that :

    This can't be inherited from the table's prefix or suffix, so you will need to use wpDataCharts callbacks to display the percentage.

    Take a look at this page, where we explained how this can be added on a chart with two Y axes. The same logic applies to one axis.

    • Every chart exposes several options that customize its look and feel. Charts usually support custom options appropriate to that visualization. wpDataChart callbacks allow adding options that are available in Google Charts APIHighcharts API, and Chart.js API
    • All necessary resources are available in charts engines API (depends on which one you use). Every engine has a different approach to chart settings. In wpDataChart callbacks, you have to adopt those settings to the wpDataChart object (you can take a look at examples for each engine in our documentation, and also in the Support help center). A huge number of examples for any area of programming can be found on stackoverflow.com (typing your problem in google and at the end add "site: stackoverflow.com" and Google will search only that website). Also, a lot of examples of charts, chart settings, and customization can be found on jsffidle.net (typing in google for example "line chart highcharts jsffidle")
    • Please note that using hooks or wpDataTable and wpDataChart callbacks requires a certain level of programming skills and included support refers only to advice.


    3.

    In regards to this visual element of the chart, to present the negative values going "Downwards" from zero, 

    9188438632.png
    2547100909.png

    as far as i am aware, we don't have anything like that available right now.


    I will double-check with our 2nd level Team if they know of any workaround to achieve something like this and we will come back to confirm that point.


    4.

    7409625488.png

    In terms of comparison of values between previous Year and this Year with percentages,

    i did not completely understand the use-case,  but i will explain how calculation logic works in our plugin.

    - First, i suggest to get acquainted with our Calculation functions here;

    and our Formula based columns here;

    -

    For example, if you need to take a value from any SUM of a column

    and you need to make percentage values from it.

    If you take the Total SUM number and place the number in a Formula manually - that is the only way to use a SUM in a Formula column at this time;

    because currently for our plugin , these are the limitations for Formula columns;

    1. One formula column cannot be used in another. This limitation can be avoided by using nested calculations.
    2. Adding a sum in the sum row for formula columns is not yet supported.
    3. Using calculation functions in formula columns is not possible

    and we are also unable to "take a SUM number", and to use it anywhere else as part of other calculations;

    we can only display it ( under the column, or with shortcode anywhere else on site).

    -

    So, for example, if i make a table like this :

    9038865494.png

    And if i take the SUM into the Formula, to make a percentage column :

    6627860634.png

    Then, if we filter the table,  the Formula column is still going to use that "fixed number" that we input ( in my case 8) ...

    -

    I hope that helped to understand how our calculations logic currently works,

    but if you can elaborate a bit more about that comparison goal,

    can you tell me how would the second table calculate the difference from the last year's Sales data, etc, and we will advise if it's possible with our plugin?

    5. Updating data, for example, from a OneDrive or SharePoint cloud source :

    At this time we don't have anything available for something like that as Data Source.

    If you would use Excel spreadsheets,

     there are two ways to create wpDataTables:

    1. Create a table linked to an existing Excel file,

    if you choose to Create a wpDataTable and upload the Excel file :

     in which case you get to select the file (upload it) that'll be used to pull the data.

     This file will be stored in ../wp-content/uploads/YEAR/MONTH/ folder. 

    The file on that location can be changed/edited, but the column structure can't. 

    So, if you have the file with the same name,

     the same number of columns, same headers, and the same order of columns - you'd be able to replace the original file with the new file, and wpDataTables would pick that up.

    -

    Alternatively, if you select "URL from any domain"

    5896577008.png

    Creating a table linked to an Excel file by providing its URL is pretty much the same as it is for creating a table by uploading an Excel file. 

    The only difference is that you would choose the URL from any domain option instead of the WordPress Media Library option under File location when creating the table,

    and when you want to edit the source file,   you don't have to edit the one that is now in your Media Library folder,

    but you can edit on the original source file.

    2. 

     If you import the file, the plugin reads the source file and creates a manual table

    Once the table is created, it is no longer linked to the source file, so changing data in the table will not show up in Excel or Google Spreadsheet, and vice-versa. 

    To modify this table, you can either switch to the Excel-like view

    and then select and copy the range of cells you want to paste from the source file, and paste it in the Excel-like view, 

    or you can import a new/edited CSV to update the table, as well, in the following ways :

    • Replace data within an existing table.
    • Add data to an existing table.
    •   Replace the entire table with new data.

    You can see more details on Updating manual tables from source files (CSV, Excel or Google sheet) here.

    6. download data from a MS Excel file with multiple tabs. My old plugin had such an option and it made it very easy to import multiple tables from one file at the same time, instead of, for example, 3 different ones.

    -

    I am sorry to disappoint you, but our plugin is currently not capable of that.

    At this time, if you use Excel files,  

    only one Excel tab from a spreadsheet can be linked to one table;

    or during importing of data to make new Manual Tables in the database,

    in that case also, it is only possible to import one tab of the spreadsheet at a time for one table.

    -

    Let me know if i missed to cover any points/questions,

    and i will still come back to confirm on point #3.

    Thank you

    Kind Regards, 

    Miloš Jovanović
    [email protected]

    Rate my support

    wpDataTables: FAQ | Facebook | Twitter | InstagramFront-end and back-end demo | Docs

    Amelia: FAQ | Facebook | Twitter | InstagramAmelia demo sites | Docs | Discord Community

    You can try wpDataTables add-ons before purchasing on these sandbox sites:

    Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables

  • Andrzej Trzcinski replied

    Thank you for a response.
    I know I can use java script to highlight values in the rows of a table.

    Unfortunately, the colorization in your plug works a bit differently, for a specific column, not for the entire row. My question is, can I use my Java script to distinguish between low high and negative values.
    I tried to copy the script to the Css table but I don't want to save it without giving an error. Perhaps there is no JavaScript support in your plugin to define table styles or color in the rows or column 
    It well be great if I can use somehow my own Script, but I am a sceptic about it :-(

    Your plugin looked like an interesting tool for me, but I think unfortunately I will have to look for something else if I fail to get around it in some way. Now I have lots of problems to thinking if there is sens to continue work with if the answer to many questions is negative... I don't know yet we well se...

    Regards
    Andy

    <br />
    <SCRIPT LANGUAGE="JavaScript">
    var elems = document.getElementsByTagName("td");
    for (var i=0, m=elems.length; i<m; i++) {

    if (elems[i].innerHTML.charAt(0) == "-") {
    elems[i].style.color="red";
    elems[i].style.backgroundColor="#ffffff";
    }
    }

    var tab = document.getElementsByTagName("table");
    for(var i=1;i<tab[0].rows.length;i++) {
        var trs = tab[0].getElementsByTagName("tr")[i];
        var maks='';
        var min="";
        for(var j=1;j<trs.cells.length;j++) {//po wierszach 
           var tmp=trs.cells[j].innerHTML.replace(',','.');
           tmp=parseFloat(tmp.substr(0, tmp.length-2));
           if(maks=='' && trs.cells[j].innerHTML!=''){
    //console.log(trs.cells[j].innerText);
    //console.log(trs.cells[j].innerText.replace(/\s/ig, ''));
              maks=tmp;
           }else{
             if(trs.cells[j].innerHTML!=''){
               if(maks<tmp) maks=tmp;
             }
           }
           if(min=='' && trs.cells[j].innerHTML!=''){
              min=tmp;
           }else{
             if(trs.cells[j].innerHTML!=''){
               if(min>tmp) min=tmp;
             }
           }
        }
    //console.log('maks: '+maks);
       
       if(min!=''){
          for(var j=1;j<trs.cells.length;j++) {//oznaczenie min
             var tmp=trs.cells[j].innerHTML.replace(',','.');
             tmp=parseFloat(tmp.substr(0, tmp.length-2));
             if(min==tmp){
             //kolorowanie minimum
                 trs.cells[j].style.color="white";
                 trs.cells[j].style.backgroundColor="red";
             }
          }
       }
        if(maks!=''){
          for(var j=1;j<trs.cells.length;j++) {//oznaczenie maks
             var tmp=trs.cells[j].innerHTML.replace(',','.');
             tmp=parseFloat(tmp.substr(0, tmp.length-2));
             if(maks==tmp){
             //kolorowanie maksium
                 trs.cells[j].style.color="white";
                 trs.cells[j].style.backgroundColor="green";
             }
          }
       }
    }

    </SCRIPT>


  • Andrzej Trzcinski replied

    Hi again. I'll torment you a bit to think about the development of the plugin. In general, the plugin is a very good idea, but it lacks a bit and this causes problems when used professionally, for example, to create hybrid analytic charts, which is the current standard.

    If it was possible to add a footer to the table, I could use JavaScript to calculate and display the values in individual rows depending on whether it is the highest lowest or negative value. These criteria are easy to modify as in my java script.

    The problem is that I can't add anything to the table in such a way that it is a footer but in a given table and not in the settings in general. Because I can create many tables with different parameters, I have to add the footer with Java script to a specific table and not globally to all of them. It's hard to predict what data you'll be working with, so it's damn hard.

    I will also stick to the possibility of importing data more adapted to modern solutions and more secure than sharing the source data with an open link.

    You yourself know that there are many such elements for further development.

    A matter of extending the chart wizard to enable more modern charts. All this needs to be refined and I hope you will work on the development of the project, because it's really worth it.

    I am still in the phase of testing the plugin and whether I will use it and buy new subscriptions depends on you and whether this project has a chance to be developed, or rather it is as it is, and you should enjoy it.

    Have lock on example screen another PS of software charts creator...

    Attached files:  Graf03.JPG

  •  1,667
    Miloš replied

    Hi, Andrzej 

    I am not a developer, and our support in general covers helping with any kind of built-in options that are available for the plugin.

    For the features/options that are still not available as built-in,

    we provide workarounds if we have them for any use-case.

    We like to do extra effort if some basic CSS/JS is needed for a solution, but what you are doing here is a higher level of customizing the plugin.

    -

    Usually, when we need different colouring of rows, columns, cells depending on cell content/values,

    we use our Conditional Formatting, which can be used to add Conditional Logic, and add CSS classes, or change colour for cells/rows/columns.

    -

    But since you wish to use your own custom Script,  we will have to check with our 2nd level Team to see how much assistance we can provide.

    We will certainly do our best.

    -

    2. In regards to adding more chart options, you can suggest this for our developers, and they will do their best to work on it in the future.

    Please feel free to search on our suggestions page

     to see if someone may be already suggested this feature. If you can't see it, feel free to add your suggestion there,  and as more people vote, the feature will move higher on the priority list.

    You can then follow our changeLog page if you'd like ( it is also available in the plugin dashboard), where we state any new features.

    and our newsletter, so you're informed about new features.

    -

    I am still waiting to see what our 2nd level Team can advise in regards to the previous questions, we will just add these new details,

    and i will report back as soon as they advise.

    Thank you

    Kind Regards, 

    Miloš Jovanović
    [email protected]

    Rate my support

    wpDataTables: FAQ | Facebook | Twitter | InstagramFront-end and back-end demo | Docs

    Amelia: FAQ | Facebook | Twitter | InstagramAmelia demo sites | Docs | Discord Community

    You can try wpDataTables add-ons before purchasing on these sandbox sites:

    Powerful Filters | Gravity Forms Integration for wpDataTables | Formidable Forms Integration for wpDataTables | Master-Detail Tables