Pivot Table - Column Order

Webix JS
#1

Hello,

I am having some issues figuring out how the columns are ordered when doing a pivot table.

given the following (assume data repeats)

[
{
                    "TotalComm"	: "Total Commission",
                    "AccountName" 	: object.Account.Name,
                    "OppName" 		: object.Name,
                    "RepName" 		: object.Sales_Rep_for_Split__r.Name,
                    "PaymentDate" 	: months[paymentDate1.getMonth()] + ' ' + paymentDate1.getFullYear(),
                    "PaymentAmount" : object.X1st_Payment_Secondary_Rep__c
}
]

If I have PaymentDate = February 2015, March 2015, June 2015, September 2015, and July 2016

My columns sort as February 2015, September 2015, March 2015, June 2015, and July 2016. The records in the array are in order from earliest month to latest month.

This is my init:

 webix.ready(function(){
        grida = webix.ui({
            container:"testA",
            view:"pivot",
            height:800,
            width:2000,
            
            data:data,
            
            fieldMap: { "OppName" : "Opportunity Name", "AccountName" : "Account Name", "RepName" : "Account Executive", "PaymentDate" : "Payment Date", "PaymentAmount" : "Payment Amount" },
            structure: {
                rows: ["TotalComm", "OppName", "AccountName", "RepName"],
                columns: ["PaymentDate"],
                values: [{ name:"PaymentAmount", operation:"sum" }],
                filters:[]
            },
            ready:function(){
                var grid = this.$$("data");
            }
        });            
    });

Any help would be greatly appreciated.

Thanks!
Tyler

#2

Hello Tyler,

Pivot does not sort columns by names. It keeps the same order as data source.

#3

Hi Maria,

I don’t believe that to be true. Here is my data source, in order:

 [
 {
  "TotalComm": "Total Commission",
  "AccountName": "Test Account",
  "OppName": "2015 Sales Commission Plan Test",
  "RepName": "Rep 1",
  "PaymentDate": "February 2015",
  "PaymentAmount": 83891.82
 },
 {
  "TotalComm": "Total Commission",
  "AccountName": "Test Account",
  "OppName": "WCA ME Std Purchase Test",
  "RepName": "Rep 1",
  "PaymentDate": "March 2015",
  "PaymentAmount": 1018.91
 },
 {
  "TotalComm": "Total Commission",
  "AccountName": "Test Account",
  "OppName": "SE Test",
  "RepName": "Rep 1",
  "PaymentDate": "March 2015",
  "PaymentAmount": 629.75
 },
 {
  "TotalComm": "Total Commission",
  "AccountName": "Test Account",
  "OppName": "WCA ME Std Purchase Test 2",
  "RepName": "Rep 1",
  "PaymentDate": "March 2015",
  "PaymentAmount": 447.52
 },
 {
  "TotalComm": "Total Commission",
  "AccountName": "Test Account",
  "OppName": "Amerigas SE Test 2",
  "RepName": "Rep 1",
  "PaymentDate": "March 2015",
  "PaymentAmount": 944.62
 },
 {
  "TotalComm": "Total Commission",
  "AccountName": "Test Account",
  "OppName": "WCA ME Std Purchase Test",
  "RepName": "Rep 2",
  "PaymentDate": "March 2015",
  "PaymentAmount": 524.89
 },
 {
  "TotalComm": "Total Commission",
  "AccountName": "Test Account",
  "OppName": "WCA ME Std Purchase Test",
  "RepName": "Rep 1",
  "PaymentDate": "June 2015",
  "PaymentAmount": 2068.7
 },
 {
  "TotalComm": "Total Commission",
  "AccountName": "Test Account",
  "OppName": "Amerigas SE Test 2",
  "RepName": "Rep 1",
  "PaymentDate": "June 2015",
  "PaymentAmount": 1917.87
 },
 {
  "TotalComm": "Total Commission",
  "AccountName": "Test Account",
  "OppName": "WCA ME Std Purchase Test",
  "RepName": "Rep 2",
  "PaymentDate": "June 2015",
  "PaymentAmount": 1065.69
 },
 {
  "TotalComm": "Total Commission",
  "AccountName": "Test Account",
  "OppName": "WCA ME Std Purchase Test 2",
  "RepName": "Rep 1",
  "PaymentDate": "June 2015",
  "PaymentAmount": 908.61
 },
 {
  "TotalComm": "Total Commission",
  "AccountName": "Test Account",
  "OppName": "2015 Sales Commission Plan Test",
  "RepName": "Rep 1",
  "PaymentDate": "September 2015",
  "PaymentAmount": 12449.45
 },
 {
  "TotalComm": "Total Commission",
  "AccountName": "Test Account",
  "OppName": "SE Test",
  "RepName": "Rep 1",
  "PaymentDate": "July 2016",
  "PaymentAmount": 1278.58
 }
]

With this, I am still getting columns in the following order: February 2015, September 2015, March 2015, June 2015, July 2015.

Any further help you can provide would be awesome. Thanks!

#4

Through further investigation, it seems as though the issue lies with the timing of the columns creation based upon groupings. If I have Account A (Jan 2015 - 44.50), Account B (February 2015 - 55.60), and Account A (March 2015 - 46.30) the columns will show Jan 2015, March 2015, February 2015 assuming I group based on Account Name. I believe this to be a bug. Can anyone replicate and confirm?

Here is a snippet that demonstrates the issue: http://webix.com/snippet/355dae0d

#5

Hi Tyler,

Thank you for the demo.

If pivot have more than one row level, column order may vary after complex grouping. We will add additional property “sort” for columns definition.So, you will have possibility to use built-in sort functions (“string”, “int”,…) or custom sorting functions (for example like your month header):


var monthFormat = webix.Date.strToDate("%F");
...
 columns: [
    {id:"PaymentYear", sort: "int"},
    {id:"PaymentMonth", sort: function(a,b) {
         a = monthFormat(a); 
         b = monthFormat(b);
         return a>b?1:(a<b?-1:0);
    }}
],
...

This feature will be available in next build.

#6

Hi Maria,

Thank you for the help! I have downloaded the new version but it seems that the library doesn’t understand the sort function as describe. Has it been added to 2.2.3?

Here is the updated snippet with the additional code: http://webix.com/snippet/475eb345

Thanks!
Tyler

#7

Sorry for confusion, this pivot package is updated on the site.
You can download the updated trial package or grab Webix 2.2.10 from the customer area.

#8

Awesome, seems to be working in every instance except one. I’m finding that when I change the grouping based on the “Click to Configure” box, the sorting I’ve defined is not respected. At this time, I’ve hidden the “Click to Configure” area so it’s not a deal breaker but I hope you might have a solution to this issue. Thanks!

#9

The syntax has changed a bit in the latest version of the pivot.
Here is the updated snippet - https://webix.com/snippet/a0ea51ab