Hey there!
When tasked with creating a dynamic pivot table app, I didn't really want to rely on external tools and instead thought to craft something with the recently rolled out dynamic columns feature within tables.
I crafted a query to grab grouped sales data from our internal Postgres server and then used Javascript to transform it into an array of grouped objects with month fields and a grant total at the end.
var data = [];
switch({{select1.value}}) {
case "YYYY-MM-DD":
data = {{pullDay.data}};
break;
case "YYYY-MM":
data = {{pullMonth.data}};
break;
case "YYYY":
data = {{pullYear.data}};
break;
}
//const data = {{pullMonth.data}}
//Generate the template for all fields
const templatePreOBJ = [["name", 0]];
for (let item in data) {
templatePreOBJ.push([data[item].Date, 0])
}
var templateOBJ = Object.fromEntries(templatePreOBJ)
//Process data and build grouped product structures.
var outputArray = [];
data.forEach((item) => {
if (!(outputArray.map((e) => e.name).includes(item.name_code))) {
var product = {...templateOBJ};
product.name = item.name_code
product[item.Date] = parseInt(item.Count)
product.grandTotal = parseInt(item.Count);
outputArray.push(product)
}
else if (outputArray.map((e) => e.name).includes(item.name_code)) {
let index = outputArray.findIndex((e) => e.name == item.name_code)
outputArray[index][item.Date] = parseInt(item.Count)
outputArray[index].grandTotal = outputArray[index].grandTotal + parseInt(item.Count)
}
})
return outputArray
Hope this helps some others who have been wanting dynamic pivot tables!
