英文:
Filter a Query forumla by SUM()
问题
我正在为我的公司创建一个销售仪表板。
我使用以下查询来列出所有我的员工及其收入：
=QUERY(Master!A2:O, "
SELECT F, SUM(O), SUM(G)
WHERE A >= date '" & text(D3,"yyyy-MM-dd") & "'
and A <= date '" & text(D5,"yyyy-MM-dd") & "'
AND UPPER(F) contains '"&$L3&"'
AND UPPER(D) contains '"&$L5&"'
GROUP BY F
ORDER BY SUM(G) DESC
LABEL SUM(G) 'Revenue', SUM(O) 'Work Hours', F 'Employee'
")
一些数据没有与具体员工和他们的工时相关联，所以我想要排除这些结果，只列出我的员工按工时和收入。
以下是这个公式的输出，右边是我想要的内容：
我想要剔除无关的在线销售和工作时间少于10小时的员工。
我尝试在“GROUP BY F”之下使用以下条件：
AND SUM(O) > 10
HAVING SUM(O) > 10
WHERE SUM(O) > 10
不知何故，如果我尝试筛选SUM()，它会被拒绝。
我该如何解决这个问题？
英文:
I'm building a sales Dashboard for my company.
I use the following QUERY to list all my employees and their revenue:
=QUERY(Master!A2:O, "
SELECT F, SUM(O), SUM(G)
WHERE A >= date '" & text(D3,"yyyy-MM-dd") & "'
and A <= date '" & text(D5,"yyyy-MM-dd") & "'
AND UPPER(F) contains '"&$L3&"'
AND UPPER(D) contains '"&$L5&"'
GROUP BY F
ORDER BY SUM(G) DESC
LABEL SUM(G) 'Revenue', SUM(O) 'Work Hours', F 'Employee'
")
Some of the data are not attached to a person and their work hours, so I'd like to get rid of these results. To only List my Employees by Work Hours and Revenue.
Here is what I get out of this formula and on the right what I'd like to have:
I would like to cut out the irelevant Online Sales and the Employees who just worked bellow 10 Work Hours.
I tried bellow "GROUP BY F":
AND SUM(O) > 10
HAVING SUM(O) > 10
WHERE SUM(O) > 10
Somehow if I try to filter a SUM() it get's rejacted..
How can I fix that?
答案1
得分: 2
try:
=QUERY(QUERY(Master!A2:O,
"select F,sum(O),sum(G)
where A >= date '" & TEXT(D3, "e-m-d") & "'
and A <= date '" & TEXT(D5, "e-m-d") & "'
and upper(F) contains '" &$L3& "'
and upper(D) contains '" &$L5& "'
group by F
order by sum(G) desc
label sum(G)'Revenue',sum(O)'Work Hours',F'Employee'"),
"where Col2 > 10
and not Col1 contains 'Online Sales'", 1)
英文:
try:
=QUERY(QUERY(Master!A2:O,
"select F,sum(O),sum(G)
where A >= date '" &TEXT(D3, "e-m-d")&"'
and A <= date '" &TEXT(D5, "e-m-d")&"'
and upper(F) contains '"&$L3&"'
and upper(D) contains '"&$L5&"'
group by F
order by sum(G) desc
label sum(G)'Revenue',sum(O)'Work Hours',F'Employee'"),
"where Col2 > 10
and not Col1 contains 'Online Sales'", 1)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论