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?
Well I found it a bit confusing, but if I've understood correctly, I have an answer that may do what you want. I'm not clear whether you want this all done on the fly, or whether you allow for the intermediate tabale results to appear in your sheet - you referred to tables 1, 2, and 3. I have a formula for the third table, but it is based on two intermediate tables. Possibly the formulas can be combined to eliminate the intermediate tables.
Consider this formula, in H9 on my sample sheet:
The first transpose/sort gets the months across the top, the second sort gets the names of all the agents, into column 1, and the arrayformula divides the # of resolutions by the # of opportunities from two intermediate tables, to fill in the FTR values.
Note that I've limited the range of the intermediate tables, but only because of including elements below them for display purposes. These ranges could be made unlimited, like the input data table.
If this is not on the right track, let us know what more you need.
See my sample sheet here. https://docs.google.com/spreadsheets/d/16otcDfnmWMEL0YpgH3aH0vn4Gip6XEY7yPDLQ9Y-83A/edit?usp=sharing