Flexible table component / Pivot Table

šŸ’¬ Feature Requests
13

It's not pretty, but it works :wink:

My challenge is that I want to show results from two different survey questions (out of a pool of hundreds) and cross tab them. So answers to question 1 will be shown as rows answers to question 2 as columns (see screenshot below).

My data set (XTab) is pretty simple:
Answer Question 1 | Answer Question 2 | user ID

So I am trying to get:

  • number of people who chose option A for Answer 1, option W for Answer 2
  • number of people who chose option B for Answer 1, option W for Answer 2
  • etc.

And am looking for two outputs: absolute numbers and percentages (see the two different tables in the screenshot below)

I am replacing null from the database with 'N/A' - that happens if someone answered Question 1, but not Question 2 (so this would be: # of people who chose option B for Answer 1, option N/A for Answer 2)

I provide two options for the end user:

  1. only consider records of people that provided answers to both questions
  2. along which dimension do I want to summarize (Row (row), Column (col) or table (table)) for the percentages?

Lastly I did get mixed up with the row and column notation, so rowLbl and colLbl might be flipped. However; it works (and sorry if it's a bit of a mess - needed to get it done ... :wink: )

let rows = {};
let rowLbl = [...new Set( XTab.data.PO_TEXT1)];
let colLbl = [...new Set( XTab.data.PO_TEXT2)];
const innerJoin = CBBoth.value;
if (innerJoin){
  rowLbl=rowLbl.filter(x=> x !='N/A');
  colLbl=colLbl.filter(x=> x !='N/A');
}
let totalTable=0;
let totalRows={};
let totalCols={};
//po_text1 ==> row labels
//po_text2 ==> col labels
for (let i=0; i<XTab.data.USER_ID.length; i++){
  if( (XTab.data.PO_TEXT1[i] != 'N/A') || !innerJoin){
    if (!rows[XTab.data.PO_TEXT1[i]]) {
      // create new row for po_text1
      rows[XTab.data.PO_TEXT1[i]] = {};     
    }
    if (!rows[XTab.data.PO_TEXT1[i]][XTab.data.PO_TEXT2[i]]) {
      // create new row for po_text1
      rows[XTab.data.PO_TEXT1[i]][XTab.data.PO_TEXT2[i]] = 0;     
    }
    if ((XTab.data.PO_TEXT2[i] != 'N/A') || !innerJoin){
      rows[XTab.data.PO_TEXT1[i]][XTab.data.PO_TEXT2[i]]++;
	    totalTable++;
      totalRows[XTab.data.PO_TEXT1[i]]= ++totalRows[XTab.data.PO_TEXT1[i]]||1;
      totalCols[XTab.data.PO_TEXT2[i]]= ++totalCols[XTab.data.PO_TEXT2[i]]||1;
    }
  }
}
let absolute = {};
let perc = {};
let total;
absolute['Option'] = [...colLbl,'Total'];
perc['Option'] = [...colLbl,'Total'];
for (let r=0; r<rowLbl.length; r++){
  absolute[rowLbl[r]]=[];
  perc[rowLbl[r]]=[];
  for (let c=0; c< colLbl.length; c++){
      switch(DDSumType.value) {
        case 'table':
          total = totalTable;
          // code block
          break;
        case 'col' :
          total = totalRows[rowLbl[r]];
          break;
        case 'row' :
          total = totalCols[colLbl[c]];
          break;
        }
    absolute[rowLbl[r]].push(rows[rowLbl[r]][colLbl[c]]||0);
    perc[rowLbl[r]].push(Math.round(1000*(rows[rowLbl[r]][colLbl[c]]||0)/total)/10);

    let ns = [];
    absolute['Option'].forEach(x => ns.push(totalCols[x]))
    absolute['Total'] = ns;
    perc['Total'] = ns.map(x => Math.round(1000*x/Object.values(totalRows).reduce((a, b) => a+b))/10);

  }
  absolute[rowLbl[r]].push(totalRows[rowLbl[r]]);
  perc[rowLbl[r]].push(Math.round(1000*totalRows[rowLbl[r]]/Object.values(totalRows).reduce((a, b) => a+b))/10);
}
absolute['Total'][absolute['Total'].length-1] = Object.values(totalRows).reduce((a, b) => a+b)
perc['Total'][absolute['Total'].length-1] = `(n = ${Object.values(totalRows).reduce((a, b) => a+b)})`

return {absolute, perc, totalCols, totalRows, rowLbl}

image
image2836Ɨ1404 219 KB