I am trying to create crosstabs pivot tables
using Google Query function
to calculate Employees First Time Resolution (FTR) rate
based on the number of Issues received while booking Opportunities
vs Total Opportunities booked
.
+---------+-------+---------+-----------+--------+-------+
| OppName | OppID | EmpName | MonthYear | Status | Issue |
+=========+=======+=========+===========+========+=======+
| abc | 1000 | alex | 2020-Jan | active | yes |
+---------+-------+---------+-----------+--------+-------+
| def | 1001 | alex | 2020-Jan | won | yes |
+---------+-------+---------+-----------+--------+-------+
| ghi | 1002 | alex | 2020-Feb | active | no |
+---------+-------+---------+-----------+--------+-------+
| jkl | 1004 | mini | 2020-Feb | lost | yes |
+---------+-------+---------+-----------+--------+-------+
| mno | 1005 | mini | 2020-Feb | won | yes |
+---------+-------+---------+-----------+--------+-------+
| pqr | 1006 | mini | 2020-Mar | active | no |
+---------+-------+---------+-----------+--------+-------+
| stu | 1007 | mini | 2020-Mar | won | yes |
+---------+-------+---------+-----------+--------+-------+
| vwx | 1008 | joe | 2020-Jan | won | no |
+---------+-------+---------+-----------+--------+-------+
| yza | 1009 | joe | 2020-Mar | lost | yes |
+---------+-------+---------+-----------+--------+-------+
OUTPUTS:
1. NO OF ISSUES:
+--------------+----------+----------+----------+
| NO OF ISSUES | 2020-Jan | 2020-Feb | 2020-Mar |
+--------------+----------+----------+----------+
| alex | 2 | | |
+--------------+----------+----------+----------+
| mini | | 1 | 1 |
+--------------+----------+----------+----------+
| TOTAL | 2 | 1 | 1 |
+--------------+----------+----------+----------+
FORMULA:
=TRANSPOSE(QUERY($A$2:$F,"select D,count(B) WHERE D IS NOT NULL AND E!='lost' AND F = 'yes' Group by D PIVOT C LABEL D 'NO OF ISSUES'",0))
...
2. TOTAL OPPORTUNITIES:
+---------------------+----------+----------+----------+
| TOTAL OPPORTUNITIES | 2020-Jan | 2020-Feb | 2020-Mar |
+---------------------+----------+----------+----------+
| alex | 2 | 1 | |
+---------------------+----------+----------+----------+
| joe | 1 | | |
+---------------------+----------+----------+----------+
| mini | | 1 | 2 |
+---------------------+----------+----------+----------+
| TOTAL | 3 | 2 | 2 |
+---------------------+----------+----------+----------+
FORMULA:
=TRANSPOSE(QUERY($A$2:$F,"select D,count(B) WHERE D IS NOT NULL AND E!='lost' Group by D PIVOT C LABEL D 'TOTAL OPPORTUNITIES'",0))
...
3. FTR%:
+--------------+----------+----------+----------+
| FTR% | 2020-Jan | 2020-Feb | 2020-Mar |
+--------------+----------+----------+----------+
| alex | | 100.00% | |
+--------------+----------+----------+----------+
| joe | 100.00% | | |
+--------------+----------+----------+----------+
| mini | | | 50.00% |
+--------------+----------+----------+----------+
| AVERAGE FTR% | 100.00% | 100.00% | 50.00% |
+--------------+----------+----------+----------+
FORMULA:
NEED QUERY FORMULA TO CALCULATE FTR AND GENERATE THIS TABLE.
So i plan to club all 3 tables into a single query formula like this once the 3rd table formula is figured out:
={
{TRANSPOSE(QUERY($A$2:$F,"select COUNT(B) WHERE D IS NOT NULL AND E!='lost' AND F = 'yes' Group by D LABEL COUNT(B) 'FTR CALC' FORMAT COUNT(B) ' '",0))};
{TRANSPOSE(QUERY($A$2:$F,"select D,COUNT(B) WHERE D IS NOT NULL AND E!='lost' AND F = 'yes' Group by D PIVOT C LABEL D 'NO OF ISSUES'",0))};
{TRANSPOSE(QUERY($A$2:$F,"select COUNT(B) WHERE D IS NOT NULL AND E!='lost' AND F = 'yes' Group by D LABEL COUNT(B) 'TOTAL'",0))};
{TRANSPOSE(QUERY($A$2:$F,"select COUNT(B) WHERE D IS NOT NULL AND E!='lost' Group by D LABEL COUNT(B) ' ' FORMAT COUNT(B) ' '",0))};
{TRANSPOSE(QUERY($A$2:$F,"select D,COUNT(B) WHERE D IS NOT NULL AND E!='lost' Group by D PIVOT C LABEL D 'TOTAL OPPORTUNITIES'",0))};
{TRANSPOSE(QUERY($A$2:$F,"select COUNT(B) WHERE D IS NOT NULL AND E!='lost' Group by D LABEL COUNT(B) 'TOTAL'",0))}
}
The formula in each cell of the 3rd table will be:
=IFERROR(IF(100%-(I2/I7)*100%=0,"",100%-(I2/I7)*100%),"")
or simply,
=100%-(I2/I7)*100% 'above 2 crosstabs starting from column H
Can someone help me generate the 3rd table?
An Apps Script solution
Since you already have an answer for the functions, which I believe will get you to the result you want, I want to present an alternative solution for your issue. Besides being far easier (for me) to go with Apps Script, it seems like a much more appropriate tool for these type of jobs.
Though I know there are many that are fans of doing everything with formulas, power to you.
I based the example on the sample spreadsheet given in the answer @kirkg13 gave, as it was much easier to follow than in the question
First, follow this tutorial if you don't know how to create an Apps Script project.
Then fill your data in a sheet like this one:
With the sheet name "InputData". The columns must be the same, though the amount of data is up to you.
Create another sheet called "Report" and leave it blank.
Copy this code into your script editor:
This will populate the "Report" tab with these dynamic tables.
This is only a framework for you to use as a learning tool, or to adapt to your specific needs.
Reference material for Apps Script