Is there any way to create a multi level pivot?

Ask the Community Get Answers
1

I am wondering is there is any way to create a multilevel pivot table in Knack? In Excel, for example, I could create a layered pivot that would calculate by both Country and by Category, producing something like below:

image
image191×176 29.6 KB

I don’t see any way to do the same thing in Knack? has anyone got any great ideas to overcome this, that doesnt require using an external reporting system?

Advanced Knack pivot tables
2

Hey @LeighBerre96926, I have a solution that converts a grid view into an advanced pivot table. I’ll probably make a video about it sometime, but feel free to DM me if you’d like to discuss.

How it works

The renderGridAsPivot function gets all records from a grid view and groups/aggregates them in a specified way.

Benefits

Benefits of this are:

  • No limitations on results that can be displayed in the pivot table
  • Allow multi-level grouping, including totals
  • All the usual pivot calculations, with the addition of unique count, and return first or last values
  • Row subtotal if an x-axis is specified
  • Display fields from connected tables
  • Nested page link when clicking on first or last values, which is handy for a expanded details/quick-edit

Original grid

This is what the grid originally looks like:

image
image810×353 16.8 KB

Revenue/quantity by year/sales rep

This is what the function call looks like to format it like below:

renderGridAsPivot(view, { 
  yAxes: ['field_23','field_21'], // Year, Salesperson
  valKeys: [
    { key: 'field_24', mode: 'sum' }, // Revenue $
    { key: 'field_74', mode: 'sum'} // Quantity
  ],
  showTotals: true
});

image
image340×766 18.2 KB

Revenue per year (x-axis) by state/sales rep

This is what the function call looks like to format it like below:

renderGridAsPivot(view, { 
  yAxes: ['field_21','field_22',], // Year, Salesperson
  xAxis: 'field_23',
  valKeys: [
    { key: 'field_24', mode: 'sum' }, // Revenue $
  ],
  showTotals: true
});

image
image1134×543 39.1 KB

Employee training course completion

It also can display the first found result, which is handy for this training matrix example when displaying the latest completion date for each employee:

renderGridAsPivot(view, { 
  yAxes: ['field_77'], // Employee
  xAxis: 'field_78', // Course
  valKeys: [
    { key: 'field_79', mode: 'last' } // Completion date
  ], 
  linkSlug: 'view-record'
});

image
image1045×526 26.9 KB

3

Mind officially blown :exploding_head:

2 Likes
4

Wow, that’s very impressive and could be just what is needed. DM sent.