Grouping data in a table like a Pivot table

[Saishkapadi]

Hi,

I'm new to retool. Here is my question.

I'm trying to build a dashboard for our internal team that reports metrics based on the jobs that are processed through our platform. We would like this data to be represented data for each month. We are using google sheets for maintaining records of the jobs that are processed. Here is a sample representation of this data:

Every job has a unique serial number. Job_Funnel column specifies in which bucket this job currently lies.(This is a dropdown in our gsheet and has 11 different values that it can take)The value column specifies the value of this job.

Here is a representation that we would like to have.

I've shown two rows here which are basically the metrics we would like to compute and this should be done for each month. For one of the metrics shown above - Jobs awarded to our vendors, here is how its computed for the month of Jan. We first find all the jobs that have ' 'Month' column = Jan and then filter out those who have 'Job_Funnel' column value = "09. Vendor Finalised/PO Issued". The count of this data would give us the the metric for Jan. Similarly for the second metric, we sum the 'Value' column of all the jobs that have Month ="Jan" and Job_Funnel ="09. Vendor Finalised/PO Issued".

We have a number of such metrics that are based on the "Job_Funnel" column of a particular job and we need to perform these calculations for each month.

How do we do this in retool?
I have connected my gsheet to retool app. But don't know how to perform these computations and transform the data to bring it into a view that I had shown in the previous image.

Any help would be highly appreciated.

[Saishkapadi]

@victoria @Kabirdas Can you please help here.

[everett_smith]

//Initialize an array that will hold the new data.
let arr = [{type: 'Jobs Awarded', Jan: 0, Feb: 0}, {type: 'Value of Jobs',Jan: 0, Feb: 0}]

/*
On line 12, data is the dataset, formated as an object containing arrays. 
It's reformatted as an array with formatDataAsArray(data) in order to map over it.
If your data is an array of objects, formatDataAsArray isn't needed.
Below we map over the data and increment the month counts of each Jobs Awarded and Value of Jobs object as neccessary based on the Month and Job_Funnel property of the element.
*/

formatDataAsArray(data).map(ele => {
if(ele.Job_Funnel == 'Vendor Finalized') {
arr[0][ele.Month] += 1
arr[1][ele.Month] += ele.Value
}
})

// Then we return the array.
return arr

Hi @Saishkapadi, If it's still helpful, here's an example of how you could generate the second dataset from the first dataset with a JS query. Note that you would need to replace the string 'Vendor Finalized' in if(ele.Job_Funnel == 'Vendor Finalized') with the correct value from your Job_Funnel column in your sample dataset.