Excel CUBEVALUE&CUBESET计数记录大于一个数 [英] Excel CUBEVALUE & CUBESET count records greater than a number
问题描述
我正在对工作簿的数据模型进行一系列查询,以按 Category_Name
检索大于一定天数(例如> = 650
).
I am writing a series of queries to my workbook's data model to retrieve the number of documents by Category_Name
which are greater than a certain numbers of days old (e.g. >=650
).
当前,此公式(在单元格 C3
中输入)针对单个 Days 的值(
= 3
)返回正确的数字.
Currently this formula (entered in celll C3
) returns the correct number for a single Days Old
value (=3
).
=CUBEVALUE("ThisWorkbookDataModel",
"[Measures].[Count of Docs]",
"[EDD_Report].[Category_Name].&["&$B2&"]",
"[EDD_Report_10-01-18].[Days Old].[34]")
如何返回 Days 的值
> = 650
的文档数?
How do I return the number of documents for Days Old
values >=650
?
工作表如下:
A B C
1 Date PL Count of Docs
2 10/1/2018 ALD 3
3 ...
更新:如下面@ama的答案所建议,步骤B中的表达式无效.
UPDATE: As suggested in @ama 's answer below, the expression in step B did not work.
不过,我使用
=CUBESET("ThisWorkbookDataModel",
"{[EDD_Report_10-01-18].[Days Old].[all].[650]:[EDD_Report_10-01-18].[Days Old].[All].[3647]}")
包含此多维数据集的单元格被引用为原始CUBEVALUE公式的第三个 Member_expression
.现在的限制是,开始和结束的值必须是 DaysOld
集合的成员.
The cell containing this cubeset is referenced as the third Member_expression
of the original CUBEVALUE formula. The limitation is now that the values for the beginning and end must be members of the Days Old
set.
这是有局限性的,因为我希望对> = 650
进行更通用的测试,并且没有办法保证 Days Old
的特定值将在查询中.
This is limiting, in that, I was hoping for a more general test for >=650
and there is no way to guarantee that specific values of Days Old
will be in the query.
推荐答案
我第一次听说CUBE,所以您让我感到好奇,我做了一些挖掘工作.绝对不是专家,但是这是我发现的:
First time I hear about CUBE, so you got me curious and I did some digging. Definitely not an expert, but here is what I found:
MDX语言应该允许您以 {[Table].[Field].[All].[LowerBound]:[Table].[Field].[All].[UpperBound]的形式提供值范围]}
.
MDX language should allow you to provide value ranges in the form of {[Table].[Field].[All].[LowerBound]:[Table].[Field].[All].[UpperBound]}
.
A.获取条目总数:
D3 =CUBEVALUE("ThisWorkbookDataModel",
"[Measures].[Count of Docs]",
"[EDD_Report].[Category_Name].&["&$B2&"]"),
"{[EDD_Report_10-01-18].[Days Old].[All]")
B.获取少于650个的条目数:
B. Get the number of entries less than 650:
E3 =CUBEVALUE("ThisWorkbookDataModel",
"[Measures].[Count of Docs]",
"[EDD_Report].[Category_Name].&["&$B2&"]"),
"{[EDD_Report_10-01-18].[Days Old].[All].[0]:[EDD_Report_10-01-18].[Days Old].[All].[649]}")
请注意,我发现了一些有关使用.[All].[650] .lag(1)}
的信息,但我认为要使其正常工作,可能需要对数据进行排序?
Note I found something about using .[All].[650].lag(1)}
but I think for it to work properly your data might need to be sorted?
C.减去
C3 =D3-E3
或者,又快又脏:
=CUBEVALUE("ThisWorkbookDataModel",
"[Measures].[Count of Docs]",
"[EDD_Report].[Category_Name].&["&$B2&"]"),
"{[EDD_Report_10-01-18].[Days Old].[All].[650]:[EDD_Report_10-01-18].[Days Old].[All].[99999]}")
希望这会有所帮助,并且让我知道,我仍然很好奇!
Hope this helps and do let me know, I am still curious!
这篇关于Excel CUBEVALUE&CUBESET计数记录大于一个数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!