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 Old 值 (=3) 返回正确的数字.p>

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 Old 值 >=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.现在的限制是开头和结尾的值必须是 Days Old 集的成员.

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

或者，选择快速而肮脏的:

Alternatively, go for the quick and dirty:

=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屋！