使用Google Query功能进行FTR计算 [英] FTR calculation using Google Query function
本文介绍了使用Google Query功能进行FTR计算的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在尝试使用 Google查询功能创建交叉表数据透视表，以根据预订机会时收到的问题数量与已预订的总机会.
+ --------- + ------- + --------- + ----------- + -------- + ------- +|OppName |OppID |EmpName |年月|现状问题+ ================= + =============================== +======= +|abc |1000 |亚历克斯|2020年1月|活跃是的+ --------- + ------- + --------- + ----------- + -------- +------- +|def |1001 |亚历克斯|2020年1月|赢了|是的+ --------- + ------- + --------- + ----------- + -------- +------- +|ghi |1002 |亚历克斯|2020年2月|活跃没有+ --------- + ------- + --------- + ----------- + -------- +------- +|jkl |1004 |迷你|2020年2月|失落是的+ --------- + ------- + --------- + ----------- + -------- +------- +|mno |1005 |迷你|2020年2月|赢了|是的+ --------- + ------- + --------- + ----------- + -------- +------- +|pqr |1006 |迷你|2020年3月|活跃没有+ --------- + ------- + --------- + ----------- + -------- +------- +|斯图|1007 |迷你|2020年3月|赢了|是的+ --------- + ------- + --------- + ----------- + -------- +------- +|vwx |1008 |乔|2020年1月|赢了|没有+ --------- + ------- + --------- + ----------- + -------- +------- +|yza |1009 |乔|2020年3月|失落是的+ --------- + ------- + --------- + ----------- + -------- +------- + 输出:
1.没有问题:+ -------------- + ---------- + ---------- + ---------- +|没有问题|2020年1月|2020年2月|2020年3月|+ -------------- + ---------- + ---------- + ---------- +|亚历克斯|2 |||+ -------------- + ---------- + ---------- + ---------- +|迷你||1 |1 |+ -------------- + ---------- + ---------- + ---------- +|总计|2 |1 |1 |+ -------------- + ---------- + ---------- + ---------- + FORMULA:
= TRANSPOSE(QUERY($ A $ 2:$ F，选择D，count(B)D不为空且E！='丢失'且F ='是'D PIVOT C分组标签D'无问题',, 0)) ...
2.总机会:+ --------------------- + ---------- + ---------- + ---------- +|机会总数|2020年1月|2020年2月|2020年3月|+ --------------------- + ---------- + ---------- + ---------- +|亚历克斯|2 |1 ||+ --------------------- + ---------- + ---------- + ---------- +|乔|1 |||+ --------------------- + ---------- + ---------- + ---------- +|迷你||1 |2 |+ --------------------- + ---------- + ---------- + ---------- +|总计|3 |2 |2 |+ --------------------- + ---------- + ---------- + ---------- + FORMULA:
= TRANSPOSE(QUERY($ A $ 2:$ F，选择D，count(B)D不为空且E！=丢失"的组，由D PIVOT C标签D总机会""，0)) ...
3.FTR％:+ -------------- + ---------- + ---------- + ---------- +|FTR％|2020年1月|2020年2月|2020年3月|+ -------------- + ---------- + ---------- + ---------- +|亚历克斯||100.00％||+ -------------- + ---------- + ---------- + ---------- +|乔|100.00％|||+ -------------- + ---------- + ---------- + ---------- +|迷你|||50.00％|+ -------------- + ---------- + ---------- + ---------- +|平均FTR％|100.00％|100.00％|50.00％|+ -------------- + ---------- + ---------- + ---------- + FORMULA:
需要查询配方来计算FTR并生成此表. 因此，一旦确定了第三张表的公式，我计划将所有3张表合并为一个查询公式:
= {{TRANSPOSE(QUERY($ A $ 2:$ F，';选择COUNT(B)D不为空且E！='丢失'并且F ='是')按D标签分组COUNT(B)'FTR CALC'格式COUNT(B)'''',, 0))};{TRANSPOSE(QUERY($ A $ 2:$ F，选择D，COUNT(B)，其中D不为空，E！='丢失'并且F ='是'，由D PIVOT C标签D表示无问题"'&，0))};{TRANSPOSE(QUERY($ A $ 2:$ F，选择COUNT(B)，其中D不为空，E！='丢失'，F ='是'，按D LABEL COUNT(B)总计"分组."，0))};{TRANSPOSE(QUERY($ A $ 2:$ F，选择COUNT(B)D不为空且E！='丢失'的分组方式D LABEL COUNT(B)''FORFORM COUNT(B)''';，0))};{TRANSPOSE(QUERY($ A $ 2:$ F，选择D，COUNT(B)，其中D不为空，并且E！='丢失的'由D PIVOT C标签D'总机会'组成，"，0))};{TRANSPOSE(QUERY($ A $ 2:$ F，选择COUNT(B)，其中D不为空，并且E！=丢失"，按D标签分组COUNT(B)'TOTAL',, 0))}}} 第三张表的每个单元格中的公式将为:
= IFERROR(IF(100％-(I2/I7)* 100％= 0，''，100％-(I2/I7)* 100％)，") 或者简单地
= 100％-(I2/I7)* 100％'从H列开始的2个交叉表上方 有人可以帮我生成第三张表吗?
解决方案
一个Apps Script解决方案
由于您已经对这些功能有了一个答案，我相信它将使您获得所需的结果，因此，我想为您的问题提供一个替代解决方案.(对我而言)除了使用Apps Script容易得多之外，对于这些类型的工作而言，它似乎是更合适的工具.
尽管我知道很多人都喜欢用公式做一切，但还是能给您带来力量.
我将示例基于@ kirkg13答案中给出的示例电子表格，因为跟问题相比，它要容易得多
首先，如果您不知道如何
具有工作表名称"InputData".尽管数据量取决于您，但列数必须相同.
创建另一个名为报告"的工作表.并留空.
将此代码复制到脚本编辑器中:
function main(){var dataRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("InputData").getDataRange()var data = dataRange.getValues()var标头= data.shift()var dateRange = getDateRange(数据)var agentData = buildAgentData(data)var reportData = buildReport(dateRange，agentData，标头)SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Report").getRange(1,1，reportData.length，reportData [0] .length).setValues(reportData)}函数getDateRange(data){var date = data.map((record)=> {return record [3]})var minDate = new Date(Math.min.apply(null，date))var maxDate = new Date(Math.max.apply(null，date))var计数器= minDatevar dateRange = []while(计数器< = maxDate){dateRange.push(新的Date(计数器))counter.setDate(counter.getDate()+ 1);}返回dateRange}函数buildAgentData(data){agentData = {}data.forEach((record)=> {变量名称=记录[2]变量日期=记录[3]var已解决=记录[6]如果(！(agentData中的名称)){agentData [name] = {[date]:{"cases":1，"resolved":resolved}}}否则，如果(日期在agentData [name]中){agentData [name] [date] ["cases"] + = 1如果(已解决== 1){agentData [名称] [日期] [已解决"] + = 1}} 别的 {agentData [name] [date] = {"cases":1，"resolved":resolved}}})返回agentData}函数buildReport(dateRange，agentData，标头){//建立资料行var机会= []；var resolutions = [];var ftr = [];for(agentData中的agent){var rowOpp = [agent];var rowRes = [agent];var rowFtr = [agent];dateRange.forEach((date)=> {试试{rowOpp.push(agentData [agent] [date] ["cases"])}catch(e){rowOpp.push("0")}；尝试{rowRes.push(agentData [agent] [date] [已解决"])}catch(e){rowRes.push("0")}；试试{rowFtr.push(agentData [agent] [date] [已解决"]/agentData [agent] [date] [案例"])}catch(e){rowFtr.push("0")}；});events.push(rowOpp);resolutions.push(rowRes);ftr.push(rowFtr);}//建筑标头var headerOpp = [机会"]var headerRes = [解决方案"]var headerFtr = ["FTR"]dateRange.forEach((date)=> {headerOpp.push(")；headerRes.push(")；headerFtr.push(")；});dateRange.unshift(");//建立最终报告var report = [dateRange，headerOpp]chance.forEach((row)=> {report.push(row)})report.push(headerRes)resolutions.forEach((row)=> {report.push(row)})report.push(headerFtr)ftr.forEach((row)=> {report.push(row)})退货报告} 这将填充报告"带有这些动态表的标签.
这只是一个框架，供您用作学习工具或适应您的特定需求.
Apps脚本的参考资料
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:
function main() {
var dataRange = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("InputData").getDataRange()
var data = dataRange.getValues()
var headers = data.shift()
var dateRange = getDateRange(data)
var agentData = buildAgentData(data)
var reportData = buildReport(dateRange, agentData, headers)
SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Report").getRange(1,1,reportData.length, reportData[0].length).setValues(reportData)
}
function getDateRange(data) {
var dates = data.map( (record) => {return record[3]} )
var minDate = new Date(Math.min.apply(null, dates))
var maxDate = new Date(Math.max.apply(null, dates))
var counter = minDate
var dateRange = []
while (counter <= maxDate) {
dateRange.push(new Date(counter))
counter.setDate(counter.getDate() + 1);
}
return dateRange
}
function buildAgentData(data) {
agentData = {}
data.forEach( (record) => {
var name = record[2]
var date = record[3]
var resolved = record[6]
if (!(name in agentData)) {
agentData[name] = {[date]:{"cases":1, "resolved":resolved}}
} else if (date in agentData[name]) {
agentData[name][date]["cases"] += 1
if (resolved == 1) {
agentData[name][date]["resolved"] += 1
}
} else {
agentData[name][date] = {"cases":1, "resolved":resolved}
}
})
return agentData
}
function buildReport(dateRange, agentData, headers) {
// BUILDING DATA ROWS
var opportunities = [];
var resolutions = [];
var ftr = [];
for(agent in agentData) {
var rowOpp = [agent];
var rowRes = [agent];
var rowFtr = [agent];
dateRange.forEach( (date) => {
try {rowOpp.push(agentData[agent][date]["cases"])}
catch(e){rowOpp.push("0")};
try {rowRes.push(agentData[agent][date]["resolved"])}
catch(e){rowRes.push("0")};
try {rowFtr.push(agentData[agent][date]["resolved"] / agentData[agent][date]["cases"])}
catch(e){rowFtr.push("0")};
});
opportunities.push(rowOpp);
resolutions.push(rowRes);
ftr.push(rowFtr);
}
// BUILDING HEADERS
var headerOpp = ["Opportunities"]
var headerRes = ["Resolutions"]
var headerFtr = ["FTR"]
dateRange.forEach( (date) => {
headerOpp.push("");
headerRes.push("");
headerFtr.push("");
});
dateRange.unshift("");
// BUILDING FINAL REPORT
var report = [dateRange,headerOpp]
opportunities.forEach((row) => {report.push(row)})
report.push(headerRes)
resolutions.forEach((row) => {report.push(row)})
report.push(headerFtr)
ftr.forEach((row) => {report.push(row)})
return report
}
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
这篇关于使用Google Query功能进行FTR计算的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
