How to place values of different types in pivot cells?

Question

i have the following json data from this i have tried to create the following pivot jqgrid

JSON

   var data = [{"id":1,"make":"toyota","model":"corolla","fuelusagecity":"17","fuelusagehwy":"12","salesaboveavg":false,"totalnumberofsales":120000,"cellvalue":"10.1","highlight":true,"mark":true,"salesdate":"2012-01-01"},{"id":2,"make":"toyota","model":"corolla","fuelusagecity":"17","fuelusagehwy":"12","salesaboveavg":false,"totalnumberofsales":100000,"cellvalue":"SUPER","highlight":false,"mark":true,"salesdate":"2012-01-20"},{"id":3,"make":"toyota","model":"belta","fuelusagecity":"15","fuelusagehwy":"10","salesaboveavg":true,"totalnumberofsales":200000,"cellvalue":"0.99","highlight":true,"mark":false,"salesdate":"2014-10-10"},{"id":4,"make":"toyota","model":"camry","fuelusagecity":"13","fuelusagehwy":"10","salesaboveavg":false,"totalnumberofsales":300000,"cellvalue":">=MARGIN","highlight":false,"mark":false,"salesdate":"2014-12-12"},{"id":5,"make":"nissan","model":"skyline","fuelusagecity":"14","fuelusagehwy":"9","salesaboveavg":true,"totalnumberofsales":500000,"cellvalue":"7.88","highlight":false,"mark":true,"salesdate":"2014-12-31"},{"id":6,"make":"nissan","model":"zx300","fuelusagecity":"10","fuelusagehwy":"8","salesaboveavg":false,"totalnumberofsales":400000,"cellvalue":"NPP","highlight":true,"mark":true,"salesdate":"2016-01-20"}];

JQGRID image

Grid image

code this link to jsfiddle has the code for it

   var data = [{"id":1,"make":"toyota","model":"corolla","fuelusagecity":"17","fuelusagehwy":"12","salesaboveavg":false,"totalnumberofsales":120000,"cellvalue":"10.1","highlight":true,"mark":true,"salesdate":"2012-01-01"},{"id":2,"make":"toyota","model":"corolla","fuelusagecity":"17","fuelusagehwy":"12","salesaboveavg":false,"totalnumberofsales":100000,"cellvalue":"SUPER","highlight":false,"mark":true,"salesdate":"2012-01-20"},{"id":3,"make":"toyota","model":"belta","fuelusagecity":"15","fuelusagehwy":"10","salesaboveavg":true,"totalnumberofsales":200000,"cellvalue":"0.99","highlight":true,"mark":false,"salesdate":"2014-10-10"},{"id":4,"make":"toyota","model":"camry","fuelusagecity":"13","fuelusagehwy":"10","salesaboveavg":false,"totalnumberofsales":300000,"cellvalue":">=MARGIN","highlight":false,"mark":false,"salesdate":"2014-12-12"},{"id":5,"make":"nissan","model":"skyline","fuelusagecity":"14","fuelusagehwy":"9","salesaboveavg":true,"totalnumberofsales":500000,"cellvalue":"7.88","highlight":false,"mark":true,"salesdate":"2014-12-31"},{"id":6,"make":"nissan","model":"zx300","fuelusagecity":"10","fuelusagehwy":"8","salesaboveavg":false,"totalnumberofsales":400000,"cellvalue":"NPP","highlight":true,"mark":true,"salesdate":"2016-01-20"}];


    var myIntTemplate = {
        formatter: "currency",
        align: "right", sorttype: "number",
        searchoptions: { sopt: ["eq", "ne", "lt", "le", "gt", "ge"] },
        formatoptions: { defaultValue: ""}};
var prevmake='', prevmodel='';
    $("#list483").jqGrid("jqPivot",
            data,
            {
                frozenStaticCols: true,
                skipSortByX: true, //true,
                useColSpanStyle: true,
                //defaultFormatting: false,
                xDimension: [
                    {/*x0*/ dataName: "make", width: 100, label: "Make" },
                    {/*x1*/ dataName: "model", width: 100, align: "center", skipGrouping: true },
                    {/*x2*/ dataName: "salesaboveavg", hidden: true, width: 50, align: "center", skipGrouping: true },
                    {/*x3*/ dataName: "fuelusagecity", width: 80, align: "center",hidden: true,
                        label: "fuel<br/>cnsumption", skipGrouping: true
                    },
                    {/*x4*/ dataName: "fuelusagehwy",
                        width: 80,
                        align: "center",
                        label: "fuel<br/>cnsumption",
                        skipGrouping: true
                    }
                ],
                yDimension: [
                    {/*y0*/ dataName: "salesdate",
                        sortorder: "desc",
                        compare: function (item1, item2) {
                          if (item1 === item2) { return 0; }
                            return item1 < item2 ? -1 : 1;
                        }
                    }],
                aggregates: [{
                    member: "cellvalue",
                    template: myIntTemplate,
                    aggregator: "max"
                }/*,
                 {
                 member: "totalnumberofsales",
                 aggregator: "count",
                 //template: "integer",
                 label: "{0}"
                 }*/]
            },
            // grid options
            {
                iconSet: "fontAwesome",
                cmTemplate: { autoResizable: true, width: 80 },
                shrinkToFit: false,
                useUnformattedDataForCellAttr: false,
                autoResizing: { compact: true },
                groupingView: {
                    groupField: ["x0"],
                    groupColumnShow: [false],
                    groupText: ["<span class='group-text'>{0}</span>"]
                },
                width: 550,
                pager: true,
                rowNum: 20,
                caption: "<b>Car sales statistics</b>",
                rowList: [5, 10, 20, 100, "10000:All"]
            }
    );

as you can see in the json array the property name cellvalue was assigned to the pivot cells. but the issue is, it display the cell values only if the cellvalue property value is a number string but it wont display cell values in the pivot cells if cellvalue property is a text string.

How do i fix this?

PS: i use the free-grid (new version of the free jqgrid)

i have more questions relating to this but primarily i need the answer for the above.

thanks

Sorry, but I don't understand your question. You defined `aggregates` with the aggregator `"max"` for `cellvalue` property. You use `formatter: "currency", sorttype: "number"`, but the input data contains the values `"10.1"`, `"SUPER"`, `"0.99"`, `">=MARGIN"`, `"7.88"`, `"NPP"`. How you imagine the execute Max aggregator over such values? How you imagine formatting the result of Max aggregator with respect of `formatter: "currency"`? — Oleg, Dec 23 '16 at 12:47
@Oleg this i have no idea over, so i've fiddled it with some data. The according to the specification i could not see that it supports string text data. and aggregator should not work for string text 'cus it supports numeric. So is there a way to insert string text values along with string numeric values to pivot cells as stated on the original post : ) — Dore.Ad, Dec 25 '16 at 02:15
I wrote detailed explanation how pivot works in the answer on your previous question. The calculation of aggregation function is the *goal* of pivot tables. Your test data, which you included in the question has no sense for any aggregation. If you do need to use some custom aggregation function, it's possible too, but you should include *more realistic* input data and describe more exactly, how you want to define the aggregators. — Oleg, Dec 25 '16 at 22:27

How to place values of different types in pivot cells?

0 Answers0